Muptiply if formula

Microsoft Office 2007 professional (aca...
July 2, 2010 at 06:02:16
Specs: WINDOWS 7
Im not from england so exscuse me for my written englisht.
i have to buid a project in university but now i have a problem.

imi tryin to use 3 times if formula in one cell

in B1

IF A1<10.000 THAN CELL B1=0

IF 10.001<A1<30.000 THAN CELL B1=(A1-10.000)*10%

IF A1>30.001 THAN B1=A1*10%


See More: Muptiply if formula

July 2, 2010 at 06:27:47
I'm not from England either. ;-)

What you are trying to build is called a Nested IF. You're not "multiplying" an IF formula, but simply having it check for multiple conditions and return a value based on what it finds.

The key thing to keep in mind is that an IF formula works from left to right and stops as soon as it finds a condition that is TRUE. Therefore you need to make sure that you put your conditions in the proper order to get the results that you want.

For example, if you want the IF to return a certain value if A1 is greater than 400, but a different value if A1 is greater than 500, then you have to check for "500" first. Since 550 is greater than 400, if you check for >400 first that condition will be TRUE and you'll never get the value you want for >500.

OK, so what you are looking for is something like this:

=IF(A1<10,0,IF(AND(A1>10.001,A1<30),(A1-10)*0.1,IF(A1>30,A1*0.1,"The value in A1 doesn't fit any of your criteria")))

What we're doing here is asking for a "value" [e.g. (A1-10)*0.1)] when a condition is TRUE and performing another IF if the condition is FALSE. As I said earlier, the IF function will evaluate the conditions from left to right until it finds a condition that is TRUE, and then return the value_if_true associated with that condition.

You'll note that the final part of the Nested IF reads "The value in A1 doesn't fit any of your criteria".

The reason for this is that you have told Excel what to do for values <10 and >10.001, as well as <30 and >30.001, but you haven't told Excel what to do if the A1 equals 10, 10.001, 30 or 30.001.

Report •
Related Solutions

Ask Question