Cant get my head around this one

M

mbys14932

Hi All,

Trying to do the IF function on a calculation, not sure if that is what
I should be using, here is the problem.

I am trying to do a calculation on a cell, with three conditions. If
the amount is less than £96 display 0, if the amount is more than £96
(but less than £136) take the difference and add 10%. Upto here is
fine =IF(A1<96,0,IF(A1>96,(A1-96)*10/100))

The problem is when I try to add another condition to do the last sum.

so if the amount in A1 is greater that £136, add 10% from £96 upto £136
and then 22% on the ress of the amount.

So the formula should be something like this, but it is not working!
=IF(A1<96,0,IF(A1>96,(A1-96)*10/100)),IF(A1>136,(A1-96)*10/100)+(A1-136)*22/100))

Thanks in advance
 
R

Ragdyer

You didn't mention what happens if A1 = 96.

You also didn't mention what to do if A1 = 136.

AND ... your formula *doesn't add* 10% to the difference, it just takes 10%
*of* the difference.

Care to clear up the differences?
--
Regards,

RD
 
D

duane

you need to test for > 136 first, because > 136 is also > 96!

=IF(A1<96,0,IF(A1>136,4+(A1-136)*22/100,(A1-96)*10/100))
 
M

mbys14932

Thanks to all, I have managed to solve the problem using the formula
posted by duane. Thanks duane. The only problem is I don't fully
understand the formula, especially the number 4 in it, what does that
do?!

=IF(A1<96,0,IF(A1>136,*_4_*+(A1-136)*22/100,(A1-96)*10/100))
 
D

David Biddulph

mbys14932 said:
Thanks to all, I have managed to solve the problem using the formula
posted by duane. Thanks duane. The only problem is I don't fully
understand the formula, especially the number 4 in it, what does that
do?!

=IF(A1<96,0,IF(A1>136,*_4_*+(A1-136)*22/100,(A1-96)*10/100))
Click to expand...

You said:
.... add 10% from £96 upto £136 ...
 
M

mbys14932

I understand the formula below,

however, the number 4, what does it do?, or can someone give me
breakdown of the formula.

=IF(A1<96,0,IF(A1>136,4+(A1-136)*22/100,(A1-96)*10/100))

I really need to know as i need to do a similar formula for anothe
workout, but with different numbers, if I understood what the 4 in th
formula did, I may be able to do it myself.

Thanks
 
D

David Biddulph

mbys14932 said:
I understand the formula below,

however, the number 4, what does it do?, or can someone give me a
breakdown of the formula.

=IF(A1<96,0,IF(A1>136,4+(A1-136)*22/100,(A1-96)*10/100))

I really need to know as i need to do a similar formula for another
workout, but with different numbers, if I understood what the 4 in the
formula did, I may be able to do it myself.
Click to expand...

When you asked the same question last month I gave you the answer:
"You said:
.... add 10% from £96 upto £136 ..."

As an exercise, try calculating what 10% of (£136 - £96) is.

Just as a reminder, your original requirement was as follows:
"so if the amount in A1 is greater that £136, add 10% from £96 upto £136
and then 22% on the ress of the amount."
 
Top