# 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

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