PDA

View Full Version : Formula

reddawn
2011-06-26, 04:50 AM
Hi all am hoping someone can help me with a formula. Here is an example of what i need. =sum(cell reference *0.18+4.50), what i want to add to the formula is if the number in the cell reference is greater than 500, i want it to add 0.15, instead of 0.18, and if it is greater than a 1000 i want it to add 0.13, instead of 0.18 or 0.15. can anyone help me with this I would appreciate it. Cheers:confused2:

Simon Lloyd
2011-06-26, 01:09 PM
Not tested but: =IF(A1<500,SUM(A1*0.18+4.5),IF(A1>=500,SUM(A1*0.15+4.5),IF(A1>=1000,SUM(A1*0.13+4.5),0)))

Bob Phillips
2011-06-26, 02:16 PM
Try =A1*IF(A1>1000,0.13,IF(A1>500,0.15,0.18))*4.5

reddawn
2011-06-28, 10:55 AM
Hi Simon

Many thanks for the formula, only had to change the amounts a little to get it to work fine.
Here's what i ended up with: =IF(A4=0,SUM(A4*0),IF(A4<501,SUM(A4*0.18+4.5),IF(A4<1001,SUM(A4*0.15+4.5),IF(A4>1000,SUM(A4*0.13+4.5),0))))
So once again thanks, couldn't have done it without your expertise.
Cheers

reddawn
2011-06-28, 10:57 AM
Thanks for the help Bob, unfortunately I could get yours to do what I wanted.
But for the reply and the formula, I am grateful

Cheers

Simon Lloyd
2011-06-28, 11:05 AM
Your welcome, but why add the IF(A4=0,SUM(A4*0), there's no need for that as multiplying by zero gives zero? in what respect did Bob's formula not work for you?

reddawn
2011-07-02, 11:27 AM
Hi Simon, sorry for the tardy reply....i added the IF(A4=0,SUM(A4*0), because i needed the formula to recognise that if there was nothing or 0 in the cell then it would register 0. With the formula you gave me it still registered 4.50 even if though there was nothing or 0 in the cell. The formula was to do with weight V rate as i'm in the freight/trucking industry and am trying to bring the business into the 21st century from the 16th, abacus just don't cut it anymore lol. bob's formula.....don't know why it didn't work but it would not calculate for some reason above 1000kg....it may be because i'm using a 2002 version of excel...but i'm not sure....it just didn't work. Once again ty for your formula...it showed me how to go about what i consider (being from the 16th century) how to start stringing formulas together.

Cheers Bill

Bob Phillips
2011-07-02, 11:42 AM
What Simon is saying is that you don't need

=IF(A4=0,SUM(A4*0),...

just

=IF(A4=0,0,...

My version with that test

=IF(A4=0,0,A4*IF(A4>1000,0.13,IF(A4>=500,0.15,0.18))+4.5)

or even

=A4*IF(A4>1000,0.13,IF(A4>=500,0.15,0.18))+(A1>0)*4.5

BTW, it is nothing t0o do with your Excel version, they all work the same here.

reddawn
2011-07-02, 09:51 PM
Hi Bob
I will try the formula you have advised of. Having not much experience with excel other than basic add, subtract etc. I used the formula I used because it worked.

it is good to know that all excel versions work the same here, that's a plus. However, your first formula was not giving me the correct totals when i applied the formula for some reason, that i am not experienced enough to work out.

For example if A1 was 1 then the total should have been 1*0.18+4.50 = 4.68, however the formula kept throwing up 0.81 as total. also if A1 was 1005 then the total should have been 1005*0.13+4.50 = 135.15, however the formula throws up a total of 587.95, so i'm not sure why it doesn't give the correct totals.

Cheers

Bob Phillips
2011-07-02, 10:34 PM
I think that was just me mis-reading the brief. I have adjusted the latest formula, see if it is right now.

reddawn
2011-07-03, 09:47 AM
TY Bob and Simon thanks for all the help it was really appreciated. Yes both yours Bob and Simon's formula work just fine now. Appreciate your having another look at it for me Bob.

Cheers Reddawn