IF function and nested functions

  • Thread starter Thread starter DA ACTREZ
  • Start date Start date
D

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
 
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.
 
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!!!!!
Click to expand...

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.
 
Sorry, but on further testing my recommendation does not work. I was using
"increasing values across the columns" -- (know wonder it worked!!)..
 
Back
Top