If statement returns correct value when true, but returns "false" when false

[josh921]

Can someone help me? I have a VERY LONG "IF" statement that is pulling information in from a separate worksheet. I have 6 columns in a row, but I only want to pull in the information that is out of my threshold. So it may be possible for the cell to be blank. However, I don't want to repeat the same information twice. I hope this makes sense.
I want the cell to either return the VALUE if true or ""(null/empty) when false.
Here is the formula that I am using:

=IF(O9='Daily Summary'!$H$16,IF('Daily Summary'!$J$16>('Daily Summary'!$J$17+'Daily Summary'!$J$18),'Daily Summary'!$J$16,IF('Daily Summary'!$L$16>('Daily Summary'!$L$17+'Daily Summary'!$L$18),'Daily Summary'!$L$16,IF('Daily Summary'!$N$16>('Daily Summary'!$N$17+'Daily Summary'!$N$18),'Daily Summary'!$N$16,IF('Daily Summary'!$P$16>('Daily Summary'!$P$17+'Daily Summary'!$P$18),'Daily Summary'!$P$16,IF('Daily Summary'!$R$16>('Daily Summary'!$R$17+'Daily Summary'!$R$18),'Daily Summary'!$R$16))))),IF(O9='Daily Summary'!$J$16,IF('Daily Summary'!$L$16>('Daily Summary'!$L$17+'Daily Summary'!$L$18),'Daily Summary'!$L$16,IF('Daily Summary'!$N$16>('Daily Summary'!$N$17+'Daily Summary'!$N$18),'Daily Summary'!$N$16,IF('Daily Summary'!$P$16>('Daily Summary'!$P$17+'Daily Summary'!$P$18),'Daily Summary'!$P$16,IF('Daily Summary'!$R$16>('Daily Summary'!$R$17+'Daily Summary'!$R$18),'Daily Summary'!$R$16)))),IF(O9='Daily Summary'!$L$16,IF('Daily Summary'!$N$16>('Daily Summary'!$N$17+'Daily Summary'!$N$18),'Daily Summary'!$N$16,IF('Daily Summary'!$P$16>('Daily Summary'!$P$17+'Daily Summary'!$P$18),'Daily Summary'!$P$16,IF('Daily Summary'!$R$16>('Daily Summary'!$R$17+'Daily Summary'!$R$18),'Daily Summary'!$R$16))),IF(O9='Daily Summary'!$N$16,IF('Daily Summary'!$P$16>('Daily Summary'!$P$17+'Daily Summary'!$P$18),'Daily Summary'!$P$16,IF('Daily Summary'!$R$16>('Daily Summary'!$R$17+'Daily Summary'!$R$18),'Daily Summary'!$R$16)),IF(O9='Daily Summary'!$P$16,IF('Daily Summary'!$R$16>('Daily Summary'!$R$17+'Daily Summary'!$R$18),'Daily Summary'!$R$16),IF(O9='Daily Summary'!$R$16,"",IF('Daily Summary'!$H$16>('Daily Summary'!$H$17+'Daily Summary'!$H$18),'Daily Summary'!$H$16,IF('Daily Summary'!$J$16>('Daily Summary'!$J$17+'Daily Summary'!$J$18),'Daily Summary'!$J$16,IF('Daily Summary'!$L$16>('Daily Summary'!$L$17+'Daily Summary'!$L$18),'Daily Summary'!$L$16,IF('Daily Summary'!$N$16>('Daily Summary'!$N$17+'Daily Summary'!$N$18),'Daily Summary'!$N$16,IF('Daily Summary'!$P$16>('Daily Summary'!$P$17+'Daily Summary'!$P$18),'Daily Summary'!$P$16,IF('Daily Summary'!$R$16>('Daily Summary'!$R$17+'Daily Summary'!$R$18),'Daily Summary'!$R$16," "))))))))))))

 

[Scott Huish]

The very first thing I would do is to try and shorten that formula. What is it supposed to do exactly?

 

[josh921]

ok...so i started with the first column (the cell to the right of this formula - O9) and its subsequent columns looking like this (each subsequent column removes the upper most "IF" statement):
=IF('Daily Summary'!$F$16>('Daily Summary'!$F$17+'Daily Summary'!$F$18),'Daily Summary'!$F$16,IF('Daily Summary'!$H$16>('Daily Summary'!$H$17+'Daily Summary'!$H$18),'Daily Summary'!$H$16,IF('Daily Summary'!$J$16>('Daily Summary'!$J$17+'Daily Summary'!$J$18),'Daily Summary'!$J$16,IF('Daily Summary'!$L$16>('Daily Summary'!$L$17+'Daily Summary'!$L$18),'Daily Summary'!$L$16,IF('Daily Summary'!$N$16>('Daily Summary'!$N$17+'Daily Summary'!$N$18),'Daily Summary'!$N$16,IF('Daily Summary'!$P$16>('Daily Summary'!$P$17+'Daily Summary'!$P$18),'Daily Summary'!$P$16,IF('Daily Summary'!$R$16>('Daily Summary'!$R$17+'Daily Summary'!$R$18),'Daily Summary'!$R$16,"")))))))

I want the subsequent columns to start where the value of O9 left off. Otherwise, the subsequent columns will return the same value as O9 until you get to the column that no longer has that column indexed.
In the case of my formula, Column O9 returns value from 'Daily Summary'!$N$16 - I want the next column to pick up from there; but, I also want the formula to work if O9 was 'Daily Summary'!$F$16.
I hope this makes sense.
If not, I can try to attach a small version of the workbook to the thread.

 

[Scott Huish]

Can you provide a sample, expected results, and can you try and explain in English, not in formula, what you are trying to accomplish?

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD> </TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1

 

[josh921]

<table border="0" cellpadding="0" cellspacing="0" width="896"><colgroup><col style="width:48pt" span="14" width="64"> </colgroup><tbody><tr><td style="vertical-align: top;">
</td><td style="vertical-align: top;">F
</td><td style="vertical-align: top;">G
</td><td style="vertical-align: top;">H
</td><td style="vertical-align: top;">I
</td><td style="vertical-align: top;">J
</td><td style="vertical-align: top;">K
</td><td style="vertical-align: top;">L
</td><td style="vertical-align: top;">M
</td><td style="vertical-align: top;">N
</td><td style="vertical-align: top;">O
</td><td style="vertical-align: top;">P
</td><td style="vertical-align: top;">Q
</td><td style="vertical-align: top;">R
</td><td style="vertical-align: top;">S
</td></tr><tr style="height:15.75pt" height="21"> <td style="vertical-align: top;">16
</td><td class="xl2299" style="height:15.75pt;width:48pt" height="21" width="64">0.85%</td> <td class="xl2298" style="border-left:none;width:48pt" width="64">▲</td> <td class="xl2289" style="border-left:none;width:48pt" width="64">0.22%</td> <td class="xl2298" style="border-left:none;width:48pt" width="64">▲</td> <td class="xl2289" style="border-left:none;width:48pt" width="64">3.23%</td> <td class="xl2298" style="border-left:none;width:48pt" width="64">▼</td> <td class="xl2289" style="border-left:none;width:48pt" width="64">1.98%</td> <td class="xl2298" style="border-left:none;width:48pt" width="64">▼</td> <td class="xl2289" style="border-left:none;width:48pt" width="64">1.10%</td> <td class="xl2298" style="border-left:none;width:48pt" width="64">▲</td> <td class="xl2289" style="border-left:none;width:48pt" width="64">1.30%</td> <td class="xl2298" style="border-left:none;width:48pt" width="64">▼</td> <td class="xl2289" style="border-left:none;width:48pt" width="64">0.07%</td> <td class="xl2288" style="border-left:none;width:48pt" width="64">▲</td> </tr> <tr style="height:15.75pt" height="21"> <td style="vertical-align: top;">17
</td><td class="xl2297" style="height:15.75pt;width:48pt" height="21" width="64">0.62%</td> <td class="xl2292" style="border-left:none;width:48pt" width="64">▲</td> <td class="xl2291" style="border-left:none;width:48pt" width="64">0.34%</td> <td class="xl2292" style="border-left:none;width:48pt" width="64">▼</td> <td class="xl2291" style="border-left:none;width:48pt" width="64">2.91%</td> <td class="xl2292" style="border-left:none;width:48pt" width="64">▼</td> <td class="xl2291" style="border-left:none;width:48pt" width="64">2.76%</td> <td class="xl2292" style="border-left:none;width:48pt" width="64">▲</td> <td class="xl2291" style="border-left:none;width:48pt" width="64">0.51%</td> <td class="xl2292" style="border-left:none;width:48pt" width="64">▲</td> <td class="xl2291" style="border-left:none;width:48pt" width="64">1.33%</td> <td class="xl2292" style="border-left:none;width:48pt" width="64">▲</td> <td class="xl2291" style="border-left:none;width:48pt" width="64">0.09%</td> <td class="xl2290" style="border-left:none;width:48pt" width="64">▲</td> </tr> <tr style="height:15.75pt" height="21"> <td style="vertical-align: top;">18
</td><td class="xl2293" style="height:15.75pt;border-top:none; width:48pt" height="21" width="64">0.29%</td> <td class="xl2294" style="border-top:none;width:48pt" width="64">
</td> <td class="xl2296" style="border-top:none;width:48pt" width="64">0.28%</td> <td class="xl2294" style="border-top:none;width:48pt" width="64">
</td> <td class="xl2296" style="border-top:none;width:48pt" width="64">0.74%</td> <td class="xl2294" style="border-top:none;width:48pt" width="64">
</td> <td class="xl2296" style="border-top:none;width:48pt" width="64">1.04%</td> <td class="xl2294" style="border-top:none;width:48pt" width="64">
</td> <td class="xl2296" style="border-top:none;width:48pt" width="64">0.29%</td> <td class="xl2294" style="border-top:none;width:48pt" width="64">
</td> <td class="xl2296" style="border-top:none;width:48pt" width="64">0.58%</td> <td class="xl2294" style="border-top:none;width:48pt" width="64">
</td> <td class="xl2296" style="border-top:none;width:48pt" width="64">0.92%</td> <td class="xl2295" style="border-top:none;width:48pt" width="64">
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="vertical-align: top;">
</td><td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.75pt" height="21"> <td style="vertical-align: top;">
</td><td style="height:15.75pt" height="21">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="vertical-align: top;">20
</td><td class="xl2304" style="height:15.0pt" height="20">1.10%</td> <td class="xl2300" style="border-left:none">1.10%</td> <td class="xl2300" style="border-left:none">1.10%</td> <td class="xl2300" style="border-left:none">1.10%</td> <td class="xl2300" style="border-left:none">1.10%</td> <td class="xl2301" style="border-left:none"> ""</td> <td class="xl2301">""
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.75pt" height="21"> <td style="vertical-align: top;">21
</td><td class="xl2305" style="height:15.75pt;border-top:none" height="21">0.51%</td> <td class="xl2302" style="border-top:none;border-left:none">0.51%</td> <td class="xl2302" style="border-top:none;border-left:none">0.51%</td> <td class="xl2302" style="border-top:none;border-left:none">0.51%</td> <td class="xl2302" style="border-top:none;border-left:none">0.51%</td> <td class="xl2303" style="border-top:none;border-left:none"> ""</td> <td class="xl2303" style="border-top:none">""
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>

 

[josh921]

Firstly, rows 20 and 21 are the result set (which would be on a separate page).

What i want to have happen is for F20 to look at F16 thru R16 and return the value in F,H,J,L,N,P,Q,or R if it is greater than row 17+18 of the column. The problem i am having in the example pictured, is that it found the problem in column N in the first "lookup".
Because column N is in the next four columns' formulas, the value repeats.
I want each subsequent column in the result set to "pick up" at the last result (regardless of where it stopped).
When I use the first formula that I gave in this thread, I get "FALSE" instead of null values. I just want it to be blank if the result isn't found.