COMPLEX "IF" FORMULAS

[Paul]

Firstly, as I'm not very experienced at excel, I'm waaaay over my head
so any help is greatly appreciated. I have 2 queries...

#1 I am trying to write a formula that basically says that if a figure
is below a certain amount then display "this", or "this" if its above
the amount.
This is what i have so far:

=IF(D2<=8000,"Target Met","BELOW Target")

From what I gather, excel doesn't like the D2 reference, it wants
something like: =IF(1<=8000,"Target Met","BELOW Target") i have no
idea!

The way ic it is that if D2 was just an entered figure it would be
cool, but as D2 is: =SUM(C2:C32) it doesn't recognise the RESULT of
that formula.

NEXT PROBLEM!....

#2 I need to add a column up and then if the amount reaches a certain
level i.e. <=8000 then I need to calculate 30% of the total sum of
that column. but if the sum is over say 12000 then the percentage
changes, how do i do that?
To make matters worse, how do i add the percentage paid at below the
8000 mark with the changed percentage at the 12000 level and on etc.
- uc i told u this was big! Heeeeeeeelp!

Thanking you in advance.
Paul

 

[RJ KELLY]

May I offer a very humble suggestion. I dont know exactly
how your data is laid out, if all the values are all in
one row or not. If they are all in 1 row you may just try
to take the column of data and select it, copy it, and do
a PASTE SPECIAL of VALUES only so that way you get your
values, and not a reference. I to have run into this
problem on WORKSHEET formulas thats why I got more in-
depth with VB. try it you'll like it.

 

[Katherine Coombs]

Hi Paul,

You can do what you're trying to do without having to make D2 equal to a
value rather than a formula.

I've set up a replica of what you've described in our post, and it's working
for me without a problem. When you try entering =if(D2<=8000,"Target
Met","BELOW Target") what is the error that you're receiving? Could it be
that the cell that you're entering the formula into is formatted as text?

Your second problem will require something akin to a vooklup. Take a look
at the help section of vlookup (or hlookup, depending on your layout). What
this will do is allow you to put together a table similar to this:

COLUMN A COLUMN B
0 5%
8000 30%
12000 50%

You can use calculate the sum of the column and multiply it by the result of
the vlookup. The vlookup will allow you to look for the sum of the column
in ColumnA and return the corresponding value in Column B.

HTH,
Katherine

 

[Cecilkumara Fernando]

Paul,
=IF(D2<=8000,"Target Met","BELOW Target")
works fine with me even with D2 is: =SUM(C2:C32)
dose D2 change when you change the value in C2
if not may be Calculation is set to manual in Tools>Options>Calculation
if so make it automatic.
for the 2# you can use a formula like
=IF(D2<8000,D2*20%+D2,IF(D2<12000,D2*30%+D2,D2*40%+D2))
which will add 20% to D2 if D2 is 0-7999
30% to D2 if D2 is 8000-11999 and
40% to D2 if D2 is 12000 and above.

HTH
Cecil

 

[Bob Phillips]

Cecil,

I think he wants the 400 at the new rate, not the full 1200, something like

=IF(D2<=800,
D2*20%,IF(D2<=1200,800*20%+(D2-800)*30%,IF(D2<=1600,800*20%+400*30*+(D2-1200
)*40%,"")))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ron Rosenfeld]

Firstly, as I'm not very experienced at excel, I'm waaaay over my head
so any help is greatly appreciated. I have 2 queries...

#1 I am trying to write a formula that basically says that if a figure
is below a certain amount then display "this", or "this" if its above
the amount.
This is what i have so far:

=IF(D2<=8000,"Target Met","BELOW Target")

From what I gather, excel doesn't like the D2 reference, it wants
something like: =IF(1<=8000,"Target Met","BELOW Target") i have no
idea!

The way ic it is that if D2 was just an entered figure it would be
cool, but as D2 is: =SUM(C2:C32) it doesn't recognise the RESULT of
that formula.

What does "doesn't like the D2 reference" mean?
You'll need to post more information. Excel works just fine with D2 as a
reference to a cell whether the cell contains a number or a formula returning a
number.




--ron

 

[xld]

More extendable

=MIN(G1,800)*20%+MAX(MIN(G1-800,400)*30%,0)+MAX(MIN(G1-1200,400)*40%,0

 

[Cecilkumara Fernando]

Bob,
If Block values and % Increments are constant no Ifs are needed
K1=Block Value 4000
K2=Starting % 18%
K3=% Increment 2%
for the amount in D4 use the formula
=K1*K3*(INT(D4/K1)-1+2*(K2/K3))*(INT(D4/K1)/2)+MOD(D4,K1)*(K2+INT(D4/K1)*K3)
Have Fun
Cecil

 

[Paul]

Thanks everyone for your posts, I'll go through each of them in turn
and try to fix my problem- I'll post back.
Thanks again.
Paul