Related:

- Multipul equations resulting in 1 answer?
- 1 11 111 pattern in c ✓ - Forum - Programming
- 192 l.168.0.1 - How-To - Network
- Unable to Access http://192.168.0.1 - How-To - Router
- 192.168.o.1.1 tp link ✓ - Forum - Network
- Windows 8.1 online - Guide

## 3 replies

rizvisa1

- Posts
- 4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- August 2, 2020

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

=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

rizvisa1

- Posts
- 4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- August 2, 2020

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.

=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.

Klis

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 :)

I also appreciate the break down explanation, hopefully it will aid me in the future and understanding other such problems :)