Formula Hel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create a formula and it is not working the formula i am trying
to create is

If C3 is < or = 37,500 mulitple by 7.5% if C3 is > 37,0001 and < or equal go
$49,500 = 12% If C3 > $49,501 and < or equal to $62,000
=20% If C3> $62,001 and < or equal to $74,500
=25%

Can anyone help? Thanks
 
Hardy,
Here is a formula with what you wrote. However, there are gaps between
37500 and 37501,49500 and 49501,62000 and 62001. In the gaps you will get a 0
(zero).
The formula
"=IF(C3<=37500,C3*0.075,IF((C3>37501*AND(C3<=49500)<C3*(0.12),IF((C3>49501*AND(C3<=62000),C3*(0.2),IF((C3>62001)*AND(C3<=74500),C3*(0.25),0))))

hth
Dennis
 
Try:

=C3*LOOKUP(C3,{0,37001,49001,62001},{0.075,0.12,0.2,0.25})

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
I take it that 37,0001 is a typo for 37,501
Also I think you need
If C3 is < or = 37,500 multiply by 7.5% if C3 is > (OR EQUAL) 37,501 and <
or equal go $49,500 = 12% If C3 > $49,501 (or EQUAL) and < or equal to
$62,000 =20% If C3> (OR EQUAL) $62,001 and < or equal to $74,500 =25%
But what happens when it is >$74,500 ?

Some alternatives that you should test:
a)
=IF(C3>62000,C3*25%,IF(C3>49500,C3*20%,IF(C3>37000,C3*12%,C3*7.5%)))
b)
=IF(C3>62000,25%,IF(C3>49500,20%,IF(C3>37500,12%,7.5%)))*C3
c)
(7.5% + 4.5%*(C3>37500) + 8%*(C3>49500) + 5%*(C3>62000))*C3
d)
=HLOOKUP(C3,{0,37501,49501,62001;0.75,0.12,0.2,0.25},2,TRUE)*C3
best wishes
 
THANK YOU..... IT WORKS PERFECT
--
Ghardy


Sandy Mann said:
Try:

=C3*LOOKUP(C3,{0,37001,49001,62001},{0.075,0.12,0.2,0.25})

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
Click to expand...
 
You are very welcome, I assume that you mean it works when you correct as
per what Bernard said, (I never even noticed the typo).

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Hardy said:
THANK YOU..... IT WORKS PERFECT
Click to expand...
 
And Bernard had temporarily forgotten about LOOKUP (no need for HLOOKUP)!
best wishes from New Scotland
 
Back
Top