Adding If Functions Gives 0

[mkeys4]

Hello,

I'm adding two if functions together (as in the result of the first if function PLUS the result of the second) and for some reason, instead of giving FALSE when the logical test fails, excel is populating the cells with a 0.

Here's my formula: =IF(SUM($B4:$L4)>=45,SUM(SUMIF($B4:$L4,LARGE($B4:$L4,{1,2,3}),$Z4:$AJ4)),FALSE)+IF(SUM($B4:$L4)>=45,SUM(SUMIF($B4:$L4,SMALL($B4:$L4,{1,2,3}),$Z4:$AJ4)),FALSE)

Since FALSE is in the equation, it doesn't make sense to my that excel is reading it as a 0. I've tried separating out the two false statements, and on their own they read as false, but when I add the two separated equation together, it gives a 0.


The worksheet is attached. I'm stumped--please help!

[JosephP]

for math purposes excel treats false as 0 and true as 1

perhaps

=IF(SUM($B4:$L4)>=45,SUM(SUMIF($B4:$L4,LARGE($B4:$L4,{1,2,3}),$Z4:$AJ4))+SUM(SUMIF($B4:$L4,SMALL($B4:$L4,{1,2,3}),$Z4:$AJ4)),FALSE)

[mkeys4]

Fantastic, thank you!

[mkeys4]

Maybe you can help me with my next problem too. If the result is FALSE, I'd like to populate the cell with the closest previous value. Like I'd like to populate AL6 with AL5. I've tried an if(iserror(vlookup but it's not working for me.

[JosephP]

replace the FALSE with the cell reference you want

[mkeys4]

The cell reference will depend on the cell. It wont always be the cell above if there are two or three false statements in a row. Maybe a vba code with if "false" would work...

[JosephP]

then we need a sample workbook to see what you mean

if you had three FALSE in a row they would all carry the same value from the previous cell using this method

[mkeys4]

The workbook is attached to the first post

[JosephP]

yes but it doesn't appear to relate to your new issue-what should appear in the results?

[mkeys4]

Oh, I'm sorry. Here you go

[JosephP]

what should appear in place of each of the FALSE values? say in AL17 specifically? (did you try my previous suggestion?)

[mkeys4]

The closest previous value in that column. So in the case of AL17, excel should pull down -.006, or AL15

[mkeys4]

Sorry, you're formula did work. Thank you

[mkeys4]

You've been so helpful. Really appreciated!!

[JosephP]

you're welcome :-)