Help with IF formula

Status
Not open for further replies.

j4ymf

Well-known Member
Joined
Apr 28, 2003
Messages
743
Office Version
  1. 365
Platform
  1. Windows
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.
(N6={1,2,3,4,5}),O6/P6
This bit is not right
=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(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?
Will this work???????
IF(N7=1,O7/3,O7/1.5
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
ABCDEFGHILMNOP
5Installation DateSeal IDFire RatingLevelLocationSeal TypeXYPenetrating ServiceSORWorking Through Grid + 0.5 UpliftIdentical sealsTotal Cost
630 Sep 2022BD-0231Working Through Grid - 60 min674Mastic Only Penetration5050Cable£12.80£6.40£19.20
730 Sep 2022BD-0230Working Through Grid - 60 min674Mastic Only Penetration5050Cable£12.80£6.401£38.403
830 Sep 2022BD-0229Working Through Grid - 60 min674Mastic Only Penetration5050Cable£12.80£6.402£57.604.5
930 Sep 2022BD-0228Working Through Grid - 60 min674Mastic Only Penetration5050Cable£12.80£6.403£76.806
1030 Sep 2022BD-0227Working Through Grid - 60 min674Mastic Only Penetration5050Cable£12.80£6.404£96.007.5
1130 Sep 2022jt-0185Working Through Grid - 60 minLevel 670Mastic Only Penetration5050Cable£12.80£6.405£115.209
Sheet1
Cell Formulas
RangeFormula
L6:L11L6=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:M11M6=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
CellConditionCell FormatStop If True
N1:N4,N6:N1048576Cell Value>=1textNO
N1:N4,N6:N1048576Cell Value>1textNO
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Duplicate to: Formula

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top