#NUM error when adding together cells that are formulas

[fazthfc]

Hi,

I am trying to add 4 cells together. All four of the cells are formulas themselves for other parts in the spreadsheet. 2 of the cells are straightforward, the other 2 I want to select the largest out of 2 other cells to add.

So I want to select the largest from either E4 and H4, the largest from O4 and L4 and then add AI4 and BC4.

Here is the formula I am using:

=SUM(LARGE((E4,H4),1),LARGE((L4,O4),1),AI4,BC4)

This returns #NUM. Is it because all of those cells are formulas of their own and the final sum is not recognizing them as values?

Thanks for any help

:-)

[ChemistB]

To sum the largest, use MAX, not LARGE
=SUM(MAX(E4,H4), MAX(L4,O4), AI4, BC4)

Large is used with a continuous range

=LARGE(E4:E5,1) or
LARGE(E4:H4,1)

[fazthfc]

To sum the largest, use MAX, not LARGE
=SUM(MAX(E4,H4), MAX(L4,O4), AI4, BC4)

Large is used with a continuous range

=LARGE(E4:E5,1) or
LARGE(E4:H4,1)

Works perfectly, thanks :-)

[fazthfc]

It seems I replied too early! It is no longer saying #NUM but for some reason it is not adding up the highest number in the MAX range. It has added up the other two only.

To test it I inserted a new column and put the formula =MAX(E4,H4) and it returned 0 even though E4=31 and H4=68.

Any ideas why?

[Jonmo1]

Sounds like E4 and H4 are not really numbers.
They're 'Numbers Stored As Text'

You said those cells are also the result of another formula?
Can you post those formulas?

[fazthfc]

Yes, I think this is where the issue lies. Those cells are the result of an IF formula and I guess it is set up as text because of the "". Is there anyway of changing it to be a number without losing the formula?

=IF(F4<12,"0", IF(F4=12,"36", IF(F4=13,"38", IF(F4=14,"40", IF(F4=15,"43", IF(F4=16,"46", IF(F4=17,"48", IF(F4=18,"51", IF(F4=19,"53", IF(F4=20,"56", IF(F4=21,"58", IF(F4=22,"60", IF(F4=23,"62", IF(F4=24,"64", IF(F4=25,"65", IF(F4=26,"68", IF(F4=27,"70", IF(F4=28,"72", IF(F4=29,"73", IF(F4=30,"73", IF(F4=31,"74", IF(F4=32,"74", IF(F4=33,"75", IF(F4=33,"75", IF(F4=34,"75", IF(F4=35,"76", IF(F4=36,"76", IF(F4=37,"77", IF(F4=38,"78", IF(F4=39,"79", IF(F4=40,"80",)))))))))))))))))))))))))))))))

[Jonmo1]

Remove all the " from around the numbers.

[Tony Valko]

To sum the largest, use MAX, not LARGE
=SUM(MAX(E4,H4), MAX(L4,O4), AI4, BC4)

Large is used with a continuous range

=LARGE(E4:E5,1) or
LARGE(E4:H4,1)

The LARGE/SMALL functions will accept multiple area references:

=LARGE((E4,H4),1)

=SMALL((A1:A5,B10:B15),1)

Although MAX/MIN will do the same thing and they're shorter.

[fazthfc]

Got it thanks.

I think LARGE will have worked had the " not been around the numbers in the original formulas. It didn't recognize the value of E4 and H4 as it was 'numbers as text'.

[ChemistB]

Thanks Tony, I had tried it using {} and not (). I'm never sure when Excel wants us to use one or the other within a given function.

[Tony Valko]

______