# Zero a cell that exceeds a limit (without altering positive cells)

• The "Age to Photo" dates are not required when the date exceeds the "Death Age"
What function would you advise to use to zero the cells which are to high.
As you can see Ann Crankshaw and Betty Birtwistle's age has exceeded the age of death and are displayed, even though they have died.
I have tried to use the IF and Max functions, but i had errors on the positive numbered cells, where they did actually calculate, but in reverse.
The white parts are the inputs .. particularly the "Photo Date" and "Age to Photo" on the right.

• I3 isn't on any cell just a blank one.. should that be on age to photo..
it still shows ages that are higher than the age to photo and still shows higher values when they are dead.

• Maybe you need to add another test.

Try:

=IF(AND(E3>0,\$I\$3>E3),"",\$I\$3-D3)

Copied down

Where there is a will there are many ways. Finding one that works for you is the challenge!

MS Excel MVP 2010-2016

• OK excellent... the "IF" & the "AND" functions worked.
The first "IF" function attempt worked, but still showed higher values.
Pike and NBVC .......Many thanks for the help.
Much appreciated

• I was wondering if there is a way to add an extra function to be able to filter gender.. Male or Female.
Basically i need to zero one or the other. Lets just say that column A is the gender choice.

• Perhaps something like:

=IF(OR(A3="F",AND(E3>0,\$I\$3>E3)),"",\$I\$3-D3)

Where A3 contains and "F" for Female and "M" for Male (obviously you can change those to suit).
In this formula if A3 contains an "F" OR both of these are true: E3>0, I3>E3, then you will get a blank...

Where there is a will there are many ways. Finding one that works for you is the challenge!

MS Excel MVP 2010-2016