CODEWITHSUNDEEP
excelexcel-formula
WaveWalker116
WaveWalker116

Reputation: 43

Combining IF and MAX

Please look at the formula below. Right now, depending on the dates the end user enters in C4 or C6, the result could come back negative, but I'd like for it to say "if negative, then 0%".

I've never been one for Nested If statements as they just seem to never work quite as well as they should and break pretty easy. I was wondering if anyone had any better ideas on how to keep this formula but maybe add MAX to it? Or maybe see if there's just a better way overall to do it?

The IF statement is basically saying "if these other cells are filled out (D column), then use those dates, if not, use the first dates (C column)."

Using Office 365.

Thanks for any help!

=IFERROR(IF(OR(D4="",D6=""),((TODAY()-$C$4)*(100/($C$6-$C$4)))/100,((TODAY()-$D$4)*(100/($D$6-$D$4)))/100),"0%")

Upvotes: 0

Views: 1004

Answers (1)

Jody Highroller
Jody Highroller

Reputation: 1029

You can just wrap the IF function in MAX. Essentially just return either the calculation or 0, whichever is higher.

=IFERROR(MAX(IF(OR(D4="",D6=""),((TODAY()-$C$4)*(100/($C$6-$C$4)))/100,((TODAY()-$D$4)*(100/($D$6-$D$4)))/100),0),"0%")

Upvotes: 2

Related Questions