Forum Discussion
Formula debug. am i using the wrong formula?
I have attached my spreadsheet to help clear up what I am doing. Basically i want this formula to look at sales/m (sales per month) then check the sale $ (price), and return a buy price based on a certain margin range. the Margin % range and test columns are for reference only and should not be part of the actual formula. So far it is working but after the first section you can see that it fails to do what i want it to. heres the formula so far; =IF(A2>20,IF(B2<=9.99,ROUNDUP(B2*0.4,0),IF(B2<=19.99,ROUNDUP(B2*0.5,0),IF(B2<=29.99,ROUNDUP(B2*0.55,0),IF(B2<=39.99,ROUNDUP(B2*0.6,0),IF(B2<=48.99,ROUNDUP(B2*0.7,0),IF(B2<=49.99,ROUNDUP(B2*0.8,0),IF(A2<20,IF(B2<=9.99,ROUNDUP(B2*0.4,0),IF(B2<=19.99,ROUNDUP(B2*0.5,0),IF(B2<=29.99,ROUNDUP(B2*0.55,0),IF(B2<=39.99,ROUNDUP(B2*0.6,0),IF(B2<=48.99,ROUNDUP(B2*0.65,0),IF(B2<=49.99,ROUNDUP(B2*0.7,0),0))))))))))))))
this formula is not complete because it fails on the second set of data, but should basically repeat with different parameters.
Katrina-
Try using a lookup table instead it will greatly simplify your formula (I've attached an example file for your reference):
=IF(A2>20,ROUNDUP(B2*VLOOKUP(B2,$K$4:$N$10,2,TRUE),0),ROUNDUP(B2*VLOOKUP(B2,$K$4:$N$10,4,TRUE),0))
11 Replies
Katrina-
Try using a lookup table instead it will greatly simplify your formula (I've attached an example file for your reference):
=IF(A2>20,ROUNDUP(B2*VLOOKUP(B2,$K$4:$N$10,2,TRUE),0),ROUNDUP(B2*VLOOKUP(B2,$K$4:$N$10,4,TRUE),0))
yes that works! thank you, never thought about doing it like that. took me a second on how to get it to copy and paste on other excel sheets and still work but i finally got that figured out.
**** edit**** i covers all the ranges but some of the values it's returning are wrong but ill see if i cant fix it from here.
Katrina-
Glad you have it working pretty good now. Let me know if you need additional assistance. I'm more than happy to help get you through the last mile! My guess is that you may need to fiddle around with the lookup tables depending on the behavior your seeing...
