Expanding a formula / adding an IF condition

[Timber5]

Currently I have this formula in one of the columns (AM) of the table:
=IFERROR(IFS(AL="No",P/AK*AJ,AL="Yes",AJ*10*0.9),"")

Now I need to add another function to it, namely: if AM<AN, then take the value from AN and put it into AM.

 

[Cubist]

Try:

=MAX(IFERROR(IFS(AL="No",P/AK*AJ,AL="Yes",AJ*10*0.9),""),AN)

 

[Timber5]

works like a charm, but in case there are #VALUE errors (relevant, when there is no data in P or AJ columns), how do I get rid of them now?

 

[Cubist]

What do you want to return?
Currently the IFERROR is handling that. It should be returning empty string and MAX will ignore that empty string and return AN.

 

[Timber5]

So first I want AN to display empty cells, if there is no data in P.
AJ, AK and AM are populated automatically (VLOOKUP) based on another sheet, but there are instances (5%) where the data is not available and those rows are empty.
Whenever there is a value in AM and it is higher than what's calculated in AN, I need AN to replace the calculation and put the value from AM.

Current formula in AN is: =MAX(IFERROR(IFS(AL3="No",P3/AK3*AJ3,AL3="Yes",AJ3*10*0.9),""),AM3)
However whenever there is an empty cell in P and/or AJ, AM is no longer empty and the #VALUE error appears.

 

[Cubist]

However whenever there is an empty cell in P and/or AJ, AM is no longer empty and the #VALUE error appears.

That doesn't sound right. All errors are being handled by IFERROR. Can you show an example?

Edit: Your formula is wrong the last part should be AN3.

=MAX(IFERROR(IFS(AL3="No",P3/AK3*AJ3,AL3="Yes",AJ3*10*0.9),""),AN3)

 

[Timber5]

OK, so this one works fine:
=IF(AM3>IFERROR(IFS(AL3="No",P3/AK3*AJ3,AL3="Yes",AJ3*10*0.9),""),AM,IFERROR(IFS(AL3="No",P3/AK3*AJ3,AL3="Yes",AJ3*10*0.9),""))

 

[Cubist]

Now I need to add another function to it, namely: if AM<AN, then take the value from AN and put it into AM.

You're not using AN at all?