IF function and nested functions

[DA ACTREZ]

I am trying to creat a formala that will express the value
of a cell if the corresponding one is empty. For example
I have the 12 months of the year in columns C-N. Cells
C4:N4 have various numbers in them. Cell p4 is the output
cell. But when D4 is empty I want the value of C4 to
appear in P4. So far I have this going up until I get to
July. This is the formula and fuction I use:

IF(C4=0,"",IF(D4=0,C4,IF(E4=0,D4,IF(F4=0,E4,IF(G4=0,F4,IF
(H4=0,G4,IF(I4=0,J4)))))))

Now this works up to 7 times within the fuction but I need
to figure out how get to work for the remainder of the
months. Please Help!!!!! Thank you!!!!!

DA ACTREZ

 

[Bob Phillips]

Try this approach instead. In P4 enter
=INDIRECT(CHAR(MAX(IF(NOT(ISBLANK(C4:N4)),COLUMN(C:N)))+64)&"4")

it's an array formula, so enter with Ctrl-Shift-Enter. You will see the
formula in the formula bar with curly brackets around it.

 

[Harlan Grove]

I am trying to creat a formala that will express the value
of a cell if the corresponding one is empty. . . . ...
IF(C4=0,"",IF(D4=0,C4,IF(E4=0,D4,IF(F4=0,E4,IF(G4=0,F4,IF
(H4=0,G4,IF(I4=0,J4)))))))

Now this works up to 7 times within the fuction but I need
to figure out how get to work for the remainder of the
months. Please Help!!!!! Thank you!!!!!

Don't use multiple IFs for this sort of thing. If you mean empty cells, don't
compare them to 0, check if they're empty.

Your formula above evaluates to the value of the cell immediately to the left of
the first empty cell found searching left to right. Following that logic, try
the following array formula.

=IF(ISBLANK(C4),"",INDEX(C4:N4,12-MAX(ISBLANK(C4:N4)*(15-COLUMN(C4:N4)))))

Note: hold down [Ctrl] and [Shift] keys before pressing [Enter] to enter array
formulas.

 

[JMay]

Try:
=SUMPRODUCT(MAX(C8:N8)*(1))
It worked for me...
HTH

 

[JMay]

Sorry, but on further testing my recommendation does not work. I was using
"increasing values across the columns" -- (know wonder it worked!!)..