Excel Autosum Query? ***EXPERT LEVEL***?

I have an excel spreadsheet that is used as a cabling request form, so cable 1, 2, 3 etc.  I would like the spreadsheet to work out the length required for each cable.  Say you have cabinet A, B and C, basket tray work sits above these cabinets.  Cabinets are split into top and bottom halves, so if a cable is requested from bottom half of A (U position 1-21), it would require a 3m to get up onto the basket tray, if its requested from the top half of cabinet A (U position 22-42), it would only require a 2m due to a shorter distance.  This is also the same for the end cabinet destination.  Once you have these 2 IFs, each cabinet width is .6 meter wide, so an example based on above:

Cabinet A top half (U position 22-42) to Cabinet C top half (U position 22-42), the spreadsheet would calculate 2m + 2m + 3 x .6m = 5.8m, but I need it to round up to the next meter, so it would request a 6m cable length.

Spreadsheet has drop down fields from data validation, eg: Field A1, you could choose cabinet A, B or C (from destination).  Field B1, you could choose U-position 1-42.  Field C1 you would choose cabinet A, B, or C again (end destination).  And lastly field D1 being U-position 1-42 again, hope that makes sense, if you can crack this you are most definitely considered Guru level of Excel lol

1 Answer

Relevance
  • 3 months ago

    I'm having trouble putting your formula into this array.   - delete delete -

    AH - STOP The presses - I think I found it! .  

    YOUR formula for two Upper choices should be (2m +2m) + (3x.6) = 4m + 1.8=5.8m  Ok, so now I get it.  I've edited my formula below.  I'm going to assume that the 3x.6 is for EVERY cable.  

    so both LOWER choices would be (3m + 3m) + (3 x .6) = 7.8 or 8m 

    And a choice of Upper and Lower is the same for each at

    (2m + 3m) + (3 x .6) = 6.8 or 7m

    [wrong formula deleted]

    use this one....: 

    Ok, so if the dropdown for B and D are a single number you might try this. 

    =IF(AND(B1<=21,D1<=21),8,IF(AND(B1<=21,D1>21),7,IF(AND(B1>21,D1<=21),7,6)))

Still have questions? Get answers by asking now.