#DIV/0 error ... Skip rows with 0.00 in formula

[mikehk]

this formula works perfectly fine when all rows have numbers
=([@LastPrice]/[@2005o])^(1/(COUNTIFS(Table3[[#Headers],[2020o]:[2005c]],"*o")))-1

when some of the cells (in rows) are 0.00 ...I get #DIV/0! error.
I had like to skip the rows with 0 and do the math.

Don't know how to do Please help.

I attach a sample file.

Thank you.

[FDibbins]

Try this...
=IF(AF5=0,"",([@LastPrice]/[@2005o])^(1/(COUNTIFS(Table3[[#Headers],[2020o]:[2005c]],"*o")))-1)

[bebo021999]

Try to drag your formula with IFERROR(formula,0)

[mikehk]

Try this...
=IF(AF5=0,"",([@LastPrice]/[@2005o])^(1/(COUNTIFS(Table3[[#Headers],[2020o]:[2005c]],"*o")))-1)

sorry, maybe I didn't explain correctly. some of the rows are 0.00 (cells AB5:AG5) ...maybe in future some of the rows (more/less)
maybe with 0 so I believe COUNT formula may suit better, but I don't know how to write one.

Thank you.

[JeteMc]

If I understand correctly the bebo's suggestion does what you want. I.E. =IFERROR(([@LastPrice]/[@2005o])^(1/(COUNTIFS(Table3[[#Headers],[2020o]:[2005c]],"*o")))-1,0)
If that doesn't produce the desired result then please tell us what you would expect to see in cell AX5 if you were doing manual calculations.

[mikehk]

Thank you Bebo and JeteMe. Your formula works fine, the COUNTIFS formula counts all cells with "o", it shouldn't count
cells that have 0.00.

I tried with AND function but don't seem to get it right...please help to modify.

(COUNTIFS(AND((Table3[[#Headers],[2020o]:[2005c]],"*o"),(Table3[2020o]:[2005c]>0)

I have attached sample file that produce the desired result.

Thank you.

[JeteMc]

Modifying Ford's formula as follows produces the desired result in cell AX5:

Please Login or Register  to view this content.

Should the formula automatically find the value in [@2008o]?
If so then try:

Please Login or Register  to view this content.

Let us know if you have any questions.

[mikehk]

Thank you soooooo much JeteMc
The 2nd formula is the one I wanted.

Thanks again.
Have a lovely day and great weekend ahead.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.