|
COMPLEX "IF" FORMULAS
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 |
COMPLEX "IF" FORMULAS
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 . |
COMPLEX "IF" FORMULAS
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 |
COMPLEX "IF" FORMULAS
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 |
COMPLEX "IF" FORMULAS
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 |
COMPLEX "IF" FORMULAS
|
COMPLEX "IF" FORMULAS
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 |
COMPLEX "IF" FORMULAS
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) |
| All times are GMT +1. The time now is 02:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com