Formula debug. am i using the wrong formula?

[katrina bethea]

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.

[#2 · Accepted solution]

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))

[#3]

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.

[#4]

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...

[#5]

here it is again. i finally got it working for all fields with a little alteration to the vlookup ranges. never thought about using VL like that, learn something new everyday. now my next problem is that values under 4$ are throwing off our ideal margins. is there a way to incorporate a function within that formula that implements a rounding not to a whole dollar? IE; 3.75,3.50,.3.25,3.00,2.75...

[#6]

Try using the Ceiling Function:

=CEILING(A2,0.25)

[#7]

been playing around with it but just cant seem to figure this one out. I've tried placing it in different locations in the formula and with different amounts but cant get it to change just to values under 4$. also tried adding another "if" formula in the function but then i get "you've entered too many arguments for this function". is it just something that will have to be done separately or somehow be done within the VL table? sorry for so many questions I'm new to excel and have been trying to teach myself but some things i don't understand the inner workings of until i see them done. i normally go to youtube or do a google search but this seems to be a tad bit to specific to find out there.

[#8]

I'm happy to help.  Just not quite sure what you're trying to do.  Can you please create an example file that has your expected results in one column.  So, I can test out a few formulas.  This will help me resolve your issue.

[#9]

I HAVE MY SITUATION DESCRIBED WITHIN THE EXCEL SHEET. IF IT CAN'T BE DONE WITHIN THE CURRENT FORMULA THEN ITS NO BIG DEAL AS I CAN DO THEM MANUALLY. THE FORMULA AS A WHOLE ALREADY SAVES ME MUCH NEEDED TIME THROUGH OUT ALL THE PRODUCTS I'M PRICING EVERYDAY.

***left caps lock on, sorry.

[#10]

Maybe try using the formula in my attached file.  I have left a few comments in it to explain what is happening.

[#11]

I see whats happening now. thank you for the help. This now works exactly for what i need it. your time and help is much appreciated. sorry for the late reply as i've been out of the office.

[#12]

Katrina-

You're quite welcome.  Thanks for taking the time to respond.  Please feel free to ask the community, additional questions as they come up.  There are a lot of smart forum members out there.  Seems like I learn something daily just from scanning through some the posts.