Hi guys, new here and I have looked around but I don't really know how to describe my problem so I'm probably not looking for the right keywords. Anyway, I'm creating a shipping spreadsheet for work, taking into account the amount of deck space required for a variable amount of product. So, some items are 1 per pallet so pretty easy to determine square footage of that. But when the user enters say, 6 pieces and I can fit 4 per pallet, I don't want it to calculate that as being SIX pallet spaces, it should only be two. So, the best I could come up with using my limited knowledge was:
=IF(M52=0,0,IF(M52<=N52,K52,IF(M52<=N52*2,K52*2,IF(M52<=N52*3,K52*3,"MORE"))))
M52 = # of pieces ordered (6 in this example)
N52 = # of pieces per pallet (4 pc in this example)
K52 = deck space required in m^2 (1.48 m^2 per pallet)
So as you can see, if my quantity is 0, my output is 0 m^2. If my quantity is 1-4, I get 1.48 m^2 (1 pallet). So 6 pieces is 2.96, my desired result. So everything is great except that when,lets say the input is 200. Well my formula only lets me go to 12 before it returns "MORE" (my placeholder value). Can anyone suggest a way to trim this formula down so I can increment this to infinity without manually retyping the formula over and over and incrementing my my multiples one at a time? Actually, I tried to just type this out one by one and it gives me an error saying I can't even use that many nested arguments. Sorry for the the long winded question, just wanted to make sure I was being clear. Thanks to anyone who took the time to read this!!
Bookmarks