0

I am trying to create a formula where a vendor can enter their box quantity minimum of 6 or 12, and then have that input be scaled *2, *3, *4 etc. in my formula to compare another column.

Currently I have a formula with a vendor whose box quantity is known, and that looks like: =IF(D9=0,0,IF(AND(D9>0,D9<=12),12,IF(AND(D9>12,D9<=24),24,D9)))

The formula is much larger going up to box quantity of 300, I am sure there is a better way to write the formula as well I just have not put time into improving it so it is not so long.

So I tried changing it where the box quantity 12 is now to be the box where the vendor can input their quantity. =IF(D9=0,0,IF(AND(D9>0,D9<=(I9*2),12,IF(AND(D9>12,D9<=(I9*3),24,D9)))

This is not working so I am wondering if there is a better way to write this formula, or to get around this error?

bonCodigo
  • 14,268
  • 1
  • 48
  • 91
Logan Gordon
  • 1
  • 1
    What about a lookup table and vlookup()? – Solar Mike May 04 '23 at 12:23
  • If you are sure about the number of box quantity then why not create a table of reference and use `LOOKUP()` or `VLOOKUP()` or `XLOOKUP()` with approximate match. That way it will be faster and not hard-coded also and no multiple nested `IF()`s – Mayukh Bhattacharya May 04 '23 at 12:23
  • It would help to help you if you provide some sample data or at least a screenshot. Reading [ask] and [repro] will help you to improve your question. – Ike May 04 '23 at 12:24
  • 2
    See https://stackoverflow.com/q/42877228/4961700 – Solar Mike May 04 '23 at 12:25
  • 1
    Welcome to StackOverflow! Have you searched the site for similar questions? Please provide [a minimal reproducible example as per the guidelines here.](https://stackoverflow.com/help/minimal-reproducible-example) FYI: for a vertical table lookup, `INDEX MATCH` is useful than VLOOKUP. – bonCodigo May 04 '23 at 12:27
  • 1
    Does this answer your question? [Excel - VLOOKUP vs. INDEX/MATCH - Which is better?](https://stackoverflow.com/questions/48348889/excel-vlookup-vs-index-match-which-is-better) – bonCodigo May 04 '23 at 12:34
  • @bonCodigo that do not answer OP's question. But the one shared by Mike Sir, that will. Because OP needs approximate match and not exact match. – Mayukh Bhattacharya May 04 '23 at 12:39
  • @MayukhBhattacharya It has the information required for a solution. Do you think otherwise? – bonCodigo May 04 '23 at 12:42
  • @bonCodigo yea it does but not sufficient for OP to resolve or understand. – Mayukh Bhattacharya May 04 '23 at 12:43
  • @MayukhBhattacharya what's the insufficiency if you may? =) – bonCodigo May 04 '23 at 12:44
  • There is no example there, only few words on how `-1` or `1` can be useful. `"But depending on the situation, using -1 or 1 as the third argument of MATCH can be very useful."` --> Only this. – Mayukh Bhattacharya May 04 '23 at 12:46

2 Answers2

1

If you put all possible values in column A, this formula will give you the next highest number than what you put in D9. It is important that the numbers in column A must be sorted from largest to smallest.

=IF(D9=0,0,INDEX(A:A,MATCH(D9,A:A,-1)))
kevin
  • 1,357
  • 1
  • 4
  • 10
  • Allow the freedom for an OP to appreciate the suggestions provided on site before we feed them with a straight out solution. – bonCodigo May 04 '23 at 12:45
-1

You want to take D9 and round it up to the nearest multiple of I9, right?

There's no single function to round up to the nearest multiple of 12, but you don't need lookups or conditional logic, either. Instead, you can scale your number down by dividing by I9, round that up to the nearest integer, and then then multiply that result by I9.

=I9*ROUNDUP(D9/I9,0)

If D9 is the number of widgets ordered, and I9 is 12 because your can fit up to 12 widgets into a box,

  • D9/I9 converts D9 into the exact number of boxes you'd theoretically need,
  • But 3.25 boxes doesn't make sense, so ROUNDUP(D9/I9,0) tells you how many boxes you'll use, and then,
  • to determine how many widgets could fit into that many boxes, you multiply the number of boxes by 12, i.e. I9*ROUNDUP(D9/I9,0)
JSmart523
  • 2,069
  • 1
  • 7
  • 17