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