Writing Multiple Formulas in a Cell

[dbutler43]

Hi,
I've got a column of cells that have values from projects in them.
I want to write a formula that based upon value i want to minus X amount of days from the delivery date column.
basically in simple terms I want to write the following:-

if value >10,000 then date-2
if value >=10,000 then date-3
if value <20,000 hen date-4
if value <30,000 then date-5

etc.....
but i want to use working days as opposed to 7 day week.

please refer to attached Excel sheet.

Contract Register(1).xlsx

[TMS]

Your requirements don't make sense. 11,000 would be > 10,000, hence date - 2. But it is also >= 10,000, so maybe date - 3?

And it is also < 20,000 and 30,000 do date - 4 and also date - 5.

You can use a nested IF for this type of calculation. And you can use the WORKDAY function to subtract working days from a start date.

https://support.microsoft.com/en-us/...6-60d494efbf33

[FDibbins]

Assuming that date-2 was supposed to be < 10000, maybe something like...
if value >10,000 then date-2
if value >=10,000 then date-3
if value <20,000 hen date-4
if value <30,000 then date-5

=date(-if(value>30000,5,if(value>....

OK so maybe you need to take another look at your rules. As TMS said, they make no sense.
A value that is >= 10000 is also < 20000 etc

[HansDouwe]

Please try =LOOKUP(value,{0;10000;20000;30000};{date-2;date-3;date-4;date-5})

[dbutler43]

Hi All,
Thank you for your replies and suggestions.
I think I may have typo error on the => it should be greater than 10,000 etc...
Will let you know which one works.

Thanks again.

[dbutler43]

Hi All,
I'm still struggling tbh.

I'm trying to get the cell that’s named manufacture completion date to change based on the value cells and the agreed delivery date cell.
I want to add a formula that says if the value cell has a value less than £10,000 then agreed delivery date is minus 2 days but using working days (5 days a week) and so on but i want to add other scenarios in the same formula for other values added below.


if value >10,000 then date-3
if value >20,000 hen date-4
if value >30,000 then date-5

please see screen shot exmaple.


Screenshot 2024-01-29 110944.jpg

[FDibbins]

That makes more sense...
(Cant easily see your cells, so Im using A! - change as needed)

=date-cell-if(A1>30 000,5,if(A1>30 000,4,if(A1>10 000,3,0)

[dbutler43]

Hi FDibbins,
Thanks for your suggestion.
unfortunately, that didn’t work.
changed the A's to relevant cells but came up with an error.

The screenshot was taken from the excel file attached to my first post if that’s any help.

[FDibbins]

Screenshot did not come through, try attaching the file again?

[dbutler43]

Hi FDibbins,
Apolgises I mean't the screenshot shown on my reply on the 01-29-24 and then excel sheet referring to my first post.

[AliGW]

Screenshots are of little use. Attach a copy of the workbook where you have attempted to implement the solution offered.

[dbutler43]

Hi AliGW,
The workbook is attached on my first post.

[FDibbins]

in Manuf col G you have an error reference - you need to fix that before doing anything else. I cant tell what it is supposed to be aiming at (maybe Master col G)?

G3=IF(Master!$F2 = "IN MANUFACTURE",Master!#REF!, "N/A")

[russkris]

Assuming the values based on M3 value of the Manufacture sheet. Going to assume the Drawn By was a list of staff(assumption)

Try this in J3 copied down, seem to work but have a look at

Please Login or Register  to view this content.