Cant get my head around this one

**mbys14932** · 05-29-2006, 04:06 PM

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

**duane** · 05-29-2006, 04:30 PM

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

Ragdyer · 05-29-2006, 04:35 PM

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> mbys14932
> ------------------------------------------------------------------------
> mbys14932's Profile:
http://www.excelforum.com/member.php...o&userid=34914
> View this thread: http://www.excelforum.com/showthread...hreadid=546523
>

Sandy Mann · 05-29-2006, 06:15 PM

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

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

--
HTH

Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk

"mbys14932" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> mbys14932
> ------------------------------------------------------------------------
> mbys14932's Profile:
> http://www.excelforum.com/member.php...o&userid=34914
> View this thread: http://www.excelforum.com/showthread...hreadid=546523
>

**mbys14932** · 05-30-2006, 03:25 PM

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

David Biddulph · 05-30-2006, 05:10 PM

"mbys14932" <[email protected]> wrote in
message news:[email protected]...
>
> 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))

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

**mbys14932** · 06-09-2006, 10:24 AM

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.

Thanks.

David Biddulph · 06-09-2006, 12:30 PM

"mbys14932" <[email protected]> wrote
in message news:[email protected]...
>
> 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.

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