Multipul equations resulting in 1 answer?

Solved/Closed
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hi all,

Recently I asked the question below:

https://ccm.net/forum/affich-348337-value-error-when-sourcing-data-from

That solved the problem I was having, however, created a new one that I hadn't considered before :(

The aim of the sum above was to establish the amount of hours accumulated per week 'over' 8 hours.

So the equation is subtracting 32 hours from a 4 day period (as Fridays there is no overtime permitted), (8x4).

The new problem is that I would like the overtime to be shown per day in the sum cell, rather than just the total per week.

So if I did 9,9,10,8 hours in the first week, that would be 4 hours overtime.

At the moment, if I just enter 9 on the first day (Monday), the responding cell remains 0. It remains 0 up until I have added the 4th day. This is because I am subtracting 32 over the week, rather than just subtracting 8 per day IF more than 8 hours have been done.

How can I illustrate this into the equation so that the responding cell calculates the overtime per day? And then establishes the total for the week in the same cell?

Here is the link to Edit Grid:

http://www.editgrid.com/explore/user/klis-test/TT_Error_Test


Please provide the formula here, should someone change something :P

The formulas are the same as in the first question, with exception to the revision from the previous answer, which is now:

H4: =sumif(F3:F4,"<>",F3:F4)

Please advise and thank you very much in advance! :D

Klis



3 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
For that you have to use what is known as array formula. In a very plain term it is just like regular formula, but it evaluates each member of data in member individually and return the resulting value. To enter this formula, you have to press CTRL + SHIFT + ENTER at the same time

=sum(if(B3:F3>8,B3:F3-8,0))

Since last you mention that you are looking to learn excel
so let me break the formula for you

an IF statement is like this. IF(CONDITION, show this if true, show this if false)
=IF(A1>35, today(), "no clue")


For conditions, you can use, AND and OR
=IF(AND(a1>45, a1<35), "a1 is between 35 and 45", "a1 is not between 35 and 45)

you can have up to 7 nested if
=IF(CONDition 1, if(condition2, true, false), if(condition3, true, false))

now to to the formula, since is it array formula, each element would be evaluated individually. It is saying that for each member in the range B3:F3, check if it is greater than 8. If it is greater than subtract it from 8 , else show 0. Once all if statements are evaluated, then add the resulting values

so internally you have
1,1,2,1 via IF statement
and then sum adds it up as 4

if you have entered array formula correctly, you should see your formula as
{=sum(if(B3:F3>8,B3:F3-8,0))}. Note you do not enter {} youself, this comes in by itself if you use CTRL + SHIFT + ENTER
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
I just thought it may be of some use too. This time we use on sum and if and not array. Remember formula take resources and array take more

=SUM(IF(b3>8,b3-8,0), IF(C3>8,C3-8,0),IF(D3>8,D3-8,0), IF(E3>8,E3-8,0), IF(F3>8,F3-8,0))

This is basically same array formula, just now has been expanded.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Thank you again Rizvisa, you have helped me greatly.
I also appreciate the break down explanation, hopefully it will aid me in the future and understanding other such problems :)