=IFERROR() "value_if_error" incongruity

[Monster Island]

Hello there. New poster here, so I'm nearly guaranteed to pose an already-asked question, I suppose. I did search for it though!

I'm using Excel 2007 with Windows XP Professional Ver 2002 SP3.

Here's a basic version of a table I'm working with.

[TABLE="width: 200"]
<tbody>[TR]
[TD]M[/TD]
[TD]M[/TD]
[TD]M[/TD]
[TD]M[/TD]
[TD]M[/TD]
[TD]SM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]SC[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]12[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]14[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So I have a table like the one above. I'm doing a COUNTA for the columns - each one represents a day - as =COUNTA(A4:A6) where you see each "C". This is intended as a quantity of cells that contain values.

Elsewhere, I have another section of the table which does another calculation of minutes spent on that particular day, which are listed where you see "M". For the sake of this formula, it doesn't matter what's in here.

"SC" is a sum of "C" values. This formula is =IFERROR(SUM(C1:C5),"----"). In my current table, this returns "0".
"SM" is a sum of "M" values. This formula is =IFERROR(SUM(A1:A5),"----"). In my current table, this also returns "0".
Elsewhere, I have the following formula: =IFERROR((SC*60/SM),"----"). This returns "----".

Why do the first two circumstances of =IFERROR return "0" while the third returns "----"?

I also welcome any input if my method seems foolish. Hopefully this is clear enough!

Thanks!

-MI

 

[Andrew Poulsom]

Welcome to MrExcel.

What are SC and SM exactly? If you remove IFERROR what's returned?

=SC*60/SM

 

[ChrisR]

Edit............

Just realised thats not it

 

[Monster Island]

Welcome to MrExcel.

What are SC and SM exactly? If you remove IFERROR what's returned?

=SC*60/SM

SC and SM are totals of other values. SM is minutes, SC is a quantity of cells that contain values (the value of the cell is irrelevant because this represents account #'s) and they're counted as individual orders.

The SC*60/SM ends up with something like 20*60/150 and represents a rate; in this case 8.0.

 

[Andrew Poulsom]

What does the formula I posted return?

 

[Monster Island]

And thank you for your prompt help!

 

[Monster Island]

In its current form, 5.88. Beyond that, I guess I don't understand what you're asking. Do you want to know a value, or do you want to know for this specific table, or do you want to know what the value is supposed to be?

 

[Weazel]

Just an observation...

For whatever reason, it appears to me that the IFERROR is not detecting an actual error with the SUM in =IFERROR(SUM(C1:C5),"----"), If the sum is returning a 0, I would guess that maybe your numbers are formatted as text, which would result in the sum returning 0 and is not an error as far as the IFERROR is concerned.

Just a thought though

 

[Andrew Poulsom]

If =IFERROR((SC*60/SM),"----") returns "----" then =SC*60/SM must return an error, not 5.88.

 

[Monster Island]

If =IFERROR((SC*60/SM),"----") returns "----" then =SC*60/SM must return an error, not 5.88.

Oh, of course. I'm sorry. It currently returns #DIV/0!.

And now I understand why you were asking.

I assume that because it's a #DIV/0 then it's going to return "----" because the Div/0 is an error, but in the case of the SC and SM you're talking about a sum, which won't return an error - it'll just return "0", which is not an error, which is why the =IFERROR doesn't work for sums.

If that's right, then thank you!