Multipul equations resulting in 1 answer?
Solved/Closed
Klis
-
17 May 2010 à 05:10
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 17 May 2010 à 07:13
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 17 May 2010 à 07:13
Related:
- Multipul equations resulting in 1 answer?
- Igi 1 free download - Download - Shooters
- Tentacle locker 1 - Download - Adult games
- Fnia 1 download - Download - Adult games
- My cute roommate 1 - Download - Adult games
- How to answer call with volume button android - Guide
3 responses
rizvisa1
Posts
4478
Registration date
Thursday 28 January 2010
Status
Contributor
Last seen
5 May 2022
766
17 May 2010 à 05:49
17 May 2010 à 05:49
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
4478
Registration date
Thursday 28 January 2010
Status
Contributor
Last seen
5 May 2022
766
17 May 2010 à 07:13
17 May 2010 à 07:13
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.