Hello
Thank you to Fluff who helped me get this far
but my initial question has changed.
When i extract the data from our software i get the Total Cost, we then need to show what the basic rate is and what the up lift is
FLUFF added the last changes but i need a little help to correct the last change.
In colulmn will eith be a 1, 2,3,4, or 5 or empty, what this is
the value in column O is the rate, column L is /1.5
What i now realise im after is if column N is if i have a identical seal and the amount
Ive manage dto show what im after but i would like the formula to work with column N and not column P
I manage to work out what im after and i would like some hellp to write the formula
IF(N7=2,O7/4.5,O7/1.5
IF(N7=3,O7/6,O7/1.5
IF(N7=4,O7/7.5,O7/1.5
IF(N7=5,O7/9,O7/1.5
Thank you in adavnce
Thank you to Fluff who helped me get this far
but my initial question has changed.
When i extract the data from our software i get the Total Cost, we then need to show what the basic rate is and what the up lift is
FLUFF added the last changes but i need a little help to correct the last change.
This bit is not right(N6={1,2,3,4,5}),O6/P6
=IF(OR(C6={"Working Through Grid - 60 min","Working Through Grid - 30 min","Working Through Grid - 120 min"}),IF(OR(N6={1,2,3,4,5}),O6/P6,O6/1.5),O6)
In colulmn will eith be a 1, 2,3,4, or 5 or empty, what this is
the value in column O is the rate, column L is /1.5
What i now realise im after is if column N is if i have a identical seal and the amount
Ive manage dto show what im after but i would like the formula to work with column N and not column P
I manage to work out what im after and i would like some hellp to write the formula
=IF(OR(C7={"Working Through Grid - 60 min","Working Through Grid - 30 min","Working Through Grid - 120 min"}),IF(N7=1,O7/3,O7/1.5),O7)
IF(N7=1,O7/3,O7/1.5Will this work???????=IF(OR(C7={"Working Through Grid - 60 min","Working Through Grid - 30 min","Working Through Grid - 120 min"}),IF(N7=1,O7/3,O7/1.5),(N7=2,O7/4.5,O7/1.5,O7) etc?
IF(N7=2,O7/4.5,O7/1.5
IF(N7=3,O7/6,O7/1.5
IF(N7=4,O7/7.5,O7/1.5
IF(N7=5,O7/9,O7/1.5
Thank you in adavnce
Copy of Pboro A - AFP 2623409 BORIS DETAILED COST BREAKDOWN.xlsx | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | L | M | N | O | P | |||||
5 | Installation Date | Seal ID | Fire Rating | Level | Location | Seal Type | X | Y | Penetrating Service | SOR | Working Through Grid + 0.5 Uplift | Identical seals | Total Cost | |||||
6 | 30 Sep 2022 | BD-0231 | Working Through Grid - 60 min | 6 | 74 | Mastic Only Penetration | 50 | 50 | Cable | £12.80 | £6.40 | £19.20 | ||||||
7 | 30 Sep 2022 | BD-0230 | Working Through Grid - 60 min | 6 | 74 | Mastic Only Penetration | 50 | 50 | Cable | £12.80 | £6.40 | 1 | £38.40 | 3 | ||||
8 | 30 Sep 2022 | BD-0229 | Working Through Grid - 60 min | 6 | 74 | Mastic Only Penetration | 50 | 50 | Cable | £12.80 | £6.40 | 2 | £57.60 | 4.5 | ||||
9 | 30 Sep 2022 | BD-0228 | Working Through Grid - 60 min | 6 | 74 | Mastic Only Penetration | 50 | 50 | Cable | £12.80 | £6.40 | 3 | £76.80 | 6 | ||||
10 | 30 Sep 2022 | BD-0227 | Working Through Grid - 60 min | 6 | 74 | Mastic Only Penetration | 50 | 50 | Cable | £12.80 | £6.40 | 4 | £96.00 | 7.5 | ||||
11 | 30 Sep 2022 | jt-0185 | Working Through Grid - 60 min | Level 6 | 70 | Mastic Only Penetration | 50 | 50 | Cable | £12.80 | £6.40 | 5 | £115.20 | 9 | ||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
L6:L11 | L6 | =IF(OR(C6={"Working Through Grid - 60 min","Working Through Grid - 30 min","Working Through Grid - 120 min"}),IF(OR(N6={1,2,3,4,5}),O6/P6,O6/1.5),O6) |
M6:M11 | M6 | =IFS(OR(C6="Working Through Grid - 60 min",C6="Working Through Grid - 30 min",C6="Working Through Grid - 120 min"),L6/2,OR(C6="60 Min",C6="30 Min",C6="Air Seal",C6="120 Min",C6="90 Min",C6="Acoustic Seal"),"") |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
N1:N4,N6:N1048576 | Cell Value | >=1 | text | NO |
N1:N4,N6:N1048576 | Cell Value | >1 | text | NO |