Cant get my head around this one
 

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(A196,(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(A196,(A1-96)*10/100)),IF(A1136,(A1-96)*10/100)+(A1-136)*22/100))

Thanks in advance


--
mbys14932
------------------------------------------------------------------------
mbys14932's Profile: http://www.excelforum.com/member.php...o&userid=34914
View this thread: http://www.excelforum.com/showthread...hreadid=546523

Cant get my head around this one
 

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

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"mbys14932" wrote
in message ...

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(A196,(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(A196,(A1-96)*10/100)),IF(A1136,(A1-96)*10/100)+(A1-136)*22/
100))

Thanks in advance


--
mbys14932
------------------------------------------------------------------------
mbys14932's Profile:
http://www.excelforum.com/member.php...o&userid=34914
View this thread: http://www.excelforum.com/showthread...hreadid=546523

Cant get my head around this one
 

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

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


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=546523

Cant get my head around this one
 

If the logic of your calculation is correct then reverse you formula:

=IF(A1136,(A1-96)*10/100+(A1-136)*22/100,IF(A196,(A1-96)*10/100,0))

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"mbys14932" wrote
in message ...

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(A196,(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(A196,(A1-96)*10/100)),IF(A1136,(A1-96)*10/100)+(A1-136)*22/100))

Thanks in advance


--
mbys14932
------------------------------------------------------------------------
mbys14932's Profile:
http://www.excelforum.com/member.php...o&userid=34914
View this thread: http://www.excelforum.com/showthread...hreadid=546523

Cant get my head around this one
 

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(A1136,*_4_*+(A1-136)*22/100,(A1-96)*10/100))


--
mbys14932
------------------------------------------------------------------------
mbys14932's Profile: http://www.excelforum.com/member.php...o&userid=34914
View this thread: http://www.excelforum.com/showthread...hreadid=546523

Cant get my head around this one
 

"mbys14932" wrote in
message ...

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(A1136,*_4_*+(A1-136)*22/100,(A1-96)*10/100))

You said:
.... add 10% from £96 upto £136 ...
--
David Biddulph

Cant get my head around this one
 

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(A1136,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.

Thanks.


--
mbys14932
------------------------------------------------------------------------
mbys14932's Profile: http://www.excelforum.com/member.php...o&userid=34914
View this thread: http://www.excelforum.com/showthread...hreadid=546523

Cant get my head around this one
 

"mbys14932" wrote
in message ...

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(A1136,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.

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."
--
David Biddulph