I keep tire prices in an Excel worksheet. The price is calculated by the following formula:
=SUM(((L2-L2*M2/100)*N2/100+(L2-L2*M2/100))*O2/100+((L2-L2*M2/100)*N2/100+(L2-L2*M2/100))+0.5)
where L is the wholesale price, M is the discount I get from my supplier, N is the VAT and O is the markup.
I want the formula to automatically add 2.5 euros for tires costing 50 euros or less (final price, not wholesale). Is there any way to do it?
one simple way to do it would be to put this formula in a cell (say X5) then in the cell X6 put the formula =if(x5<50,x5+2.5,x5). You can of course do this for a whole column of prices.
If you don’t want the preliminary calculation to show, you can hide that column. You could also do it in one cell or column by putting the entire formula you use in place of each of the three x5’s above.
[QUOTE=OldGuy]
one simple way to do it would be to put this formula in a cell (say X5) then in the cell X6 put the formula =if(x5<50,x5+2.5,x5). You can of course do this for a whole column of prices.
Small correction you want: =if(x5<=50,x5+2.5,x5)
or =if(x5>50,x5,x5+2.5)
since you want to add the 2.5 euros if the price is 50
The simplest way is to put that formula in one cell, call it column P, and then put another formula in column Q which reads =P2+IF(P2<=50,2.5,0). Then use column Q as your final final price.
You can combine it all into one giant formula in column P just by pasting your current formula into both places P2 appears in my formula. That saves a column, but causes the computation to happen twice, which isn’t efficient. it also makes it easier to create a bug later because you have to ensure any changes to the formula are made identically in two places.
Better to use a second column. Note yo can als replace the 50 and 2.5 values with absolute references to other cells outside your table so you can change the threshold and the override increment for all tires just by changing the value in one cell.