Forum Discussion

katrina bethea's avatar
katrina bethea
Brass Contributor
May 23, 2018
Solved

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 c...
Formulas & Functions
Need Help
  • Matt Mickle's avatar
    Matt Mickle
    May 23, 2018

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

     

     

Matt Mickle's avatar
Matt Mickle
Bronze Contributor
May 23, 2018

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

 

 

  • katrina bethea's avatar
    katrina bethea
    Brass Contributor
    May 23, 2018

    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.

    • Matt Mickle's avatar
      Matt Mickle
      Bronze Contributor
      May 23, 2018

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

      • katrina bethea's avatar
        katrina bethea
        Brass Contributor
        May 24, 2018

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

Resources