+ Reply to Thread
Results 1 to 3 of 3

Incremental increases within formula

  1. #1
    Registered User
    Join Date
    03-11-2017
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    4

    Incremental increases within formula

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

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,450

    Re: Incremental increases within formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    03-11-2017
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    4

    Re: Incremental increases within formula

    Quote Originally Posted by mehmetcik View Post
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Worked like an absolute charm. Will definitely be researching this ceiling formula. So grateful, thank you so much.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Dragging a formula that increases in count, but doesn't skip
    By tmillo in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-20-2016, 09:35 AM
  2. Simple FV formula with payment that increases by x%
    By rham243 in forum Excel General
    Replies: 1
    Last Post: 04-06-2016, 06:14 PM
  3. [SOLVED] Numerical values increases as name increases
    By Jbelle7435 in forum Excel General
    Replies: 3
    Last Post: 09-16-2014, 01:41 PM
  4. Replies: 3
    Last Post: 09-28-2013, 05:18 PM
  5. [SOLVED] Increasing one cells value based on incremental increases in another
    By AndyPandy in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-19-2013, 08:45 AM
  6. Replies: 6
    Last Post: 01-30-2009, 12:38 PM
  7. Sorting increases formula result by 1
    By AndiB in forum Excel General
    Replies: 0
    Last Post: 03-10-2005, 05:54 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1