CharneLewis
New Member
- Joined
- Jun 30, 2021
- Messages
- 20
- Office Version
- 365
- Platform
- Windows
Hallo,
So I need a formula which will do the following:
1-0=1
2-1=1
3-1=2
4-2=2
5-2=3
6-3=3
7-3=4
8-4=4
The reason for this is because I'm working with scaffolding forming "square gaps" (the table given represents the scaffolding) and in this case every 2nd "window" needs an extra pipe, in other words all the odd numbers.
If you maybe have formulas for cases where very 3rd and 4th "window" needs an extra pipe, that will be helpful too. Each case should be done in it's own cell, see below.
Here is the minisheet of the table i need to input the formula.
Column Info:
C - shows the different cases (as i mentioned).
D - shows how many gaps there are.
G - needs the formula i requested.
In G7:G8 I tried formulas for cases 3 and 4.
Ignore H, E and F
So I need a formula which will do the following:
1-0=1
2-1=1
3-1=2
4-2=2
5-2=3
6-3=3
7-3=4
8-4=4
The reason for this is because I'm working with scaffolding forming "square gaps" (the table given represents the scaffolding) and in this case every 2nd "window" needs an extra pipe, in other words all the odd numbers.
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
If you maybe have formulas for cases where very 3rd and 4th "window" needs an extra pipe, that will be helpful too. Each case should be done in it's own cell, see below.
Here is the minisheet of the table i need to input the formula.
Column Info:
C - shows the different cases (as i mentioned).
D - shows how many gaps there are.
G - needs the formula i requested.
In G7:G8 I tried formulas for cases 3 and 4.
Ignore H, E and F
05 Support and soffit quotes 6.xlsx | |||||||||
---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | |||
3 | Standard Window Size | ||||||||
4 | Possible Outcomes | Case | Total gaps | Total pipes in Row | Total Ledgers | ||||
5 | case | 1 | 9 | 9 | 34 | ||||
6 | case | 2 | 5 | 2 | 34 | ||||
7 | case | 3 | 9 | 3 | 34 | ||||
8 | case | 4 | 9 | 3 | 34 | ||||
Long Bracing Window Qty 05 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D5,D7:D8 | D5 | =COUNTIF('Ledger make-ups 04'!$E$4:$E$53,">0") |
G5 | G5 | =D5 |
H5:H8 | H5 | =SUM('Ledger make-ups 04'!$G$4:$G$53) |
G6 | G6 | =ROUNDUP(IFS(E6=1,D6-(D6/2),E6=0,D6-(D6-2)),0) |
G7 | G7 | =ROUNDUP(D7-(D7/3)*2,0) |
G8 | G8 | =ROUNDUP(D8/C8,0) |
B6:B7 | B6 | =B5 |
B8 | B8 | =B5 |