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

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.



-----Original Message-----
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
.

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


"Paul" wrote in message
om...
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

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

"Paul" wrote in message
om...
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

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)

"Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message
...
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 ToolsOptionsCalculation
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

"Paul" wrote in message
om...
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

More extendable

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

--
Message posted from http://www.ExcelForum.com

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

"Bob Phillips" wrote in message
...
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)

On 20 May 2004 21:06:38 -0700, (Paul) wrote:

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

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

Ron Rosenfeld wrote in message . ..
On 20 May 2004 21:06:38 -0700, (Paul) wrote:

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