Office solution doesn't work in GSheets. Why?

[dushkin]

Hi,

I have the fowllowing use case:

Every third cell in a row may contain only one of the follwoing: 0, >0 and blank(I'll mark it as _) values .

Some examples:

C1 = _
C2 = 0 F2 = _
C3 = 0 F3 = 0 I3 = _
C4 = 125 F4 = _
C5 = 0 F5 = 323
C6 = 0 F6 = 0 I6 = 445


All the values in between (for A,B, D, E etc. are not blank but irrelevant for me)

My output should be as follows:

Unless there is a >0 value in one of these cells ==> "NA"
If there is a >0 value in one of these cells ==> The number of 0's in the row + 1

Sם, for the rows above, the expected out puts should be as follows:

NA
NA
NA
1
2
3


I was trying to apply the following formula on GSheets but it doesn't work for me:

=IFERROR(AGGREGATE(14,6,{1,2,3}/(INDEX($A1:$I4,,{3,6,9})>0),1),"NA")

What is the error?

Thanks

[oeldere]

For the college forum members.

This is the earlier (related) question.

https://www.excelforum.com/excel-gen...-in-a-row.html

[dushkin]

Yes I know. It might not be the most elegant solution and obviously it is not a generic solution, but this one works for me:

=IF(ISBLANK(E50), "NA", IF(E50 > 0, 1, IF(ISBLANK(H50), "NA", IF(H50 > 0, 2, IF(ISBLANK(K50), "NA", IF(K50 > 0, 3, IF(ISBLANK(N50), "NA", IF(N50 > 0, 4, IF(ISBLANK(Q50), "NA", IF(Q50 > 0, 5, IF(ISBLANK(T50), "NA", IF(T50 > 0, 6, "NA"))))))))))))

If you have a shorter way - I'll be glad to hear about.

[FDibbins]

Not necessarily shorter...
=IF(E50="", "NA", IF(E50 > 0, 1, IF(H50="", "NA", IF(H50 > 0, 2, IF(K50="", "NA", IF(K50 > 0, 3, IF(N50="", "NA", IF(N50 > 0, 4, IF(Q50="", "NA", IF(Q50 > 0, 5, IF(T50="", "NA", IF(T50 > 0, 6, "NA"))))))))))))