1. Formula

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   Reply With Quote

2. 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)))  Reply With Quote

3. Try =A1*IF(A1>1000,0.13,IF(A1>500,0.15,0.18))*4.5  Reply With Quote

4. 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  Reply With Quote

5. 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  Reply With Quote

6. 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?  Reply With Quote

7. 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  Reply With Quote

8. 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.  Reply With Quote

9. 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  Reply With Quote

10. I think that was just me mis-reading the brief. I have adjusted the latest formula, see if it is right now.  Reply With Quote Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•