Hi all,
I'm a newbie to this forum and although I've used Excel on and off for a number of years, I'd describe myself as low intermediate, maybe high beginner.
I've attached a spreadsheet to explain my problem.
I have two workbooks, one for the original data and one referencing what I need from that data to create reports. I want to be able to display Max Min and Median but don't want to include instances where no data is submitted (so no zeroes).
If the cell is blank as in the top table then everything works well. But where it is a reference as in the middle table #VALUE errors mess it up.
The bottom table is not suitable because I can't have zeroes because I need to do the same calculations (max,min and median) on some of those columns.

I'm using a Mac and Excel for Mac 2008.
Cheers
Rob

You should introduce the ISERROR function for your formulas in column V so that your Max, Mean and Median won't return an error.

Like, V3 should have:
=IF(ISERROR(IF(SUM(B3+D3+F3+H3+J3+P3+R3+T3)0,SUM( B3+D3+F3+H3+J3+P3+R3+T3),"")),"",IF(SUM(B3+D3+F3+H 3+J3+P3+R3+T3)0,SUM(B3+D3+F3+H3+J3+P3+R3+T3),""))

Thanks Wicked. That works great for the top and bottom table but returns "" in all rows of the middle table. I'm sure there's probably a simple solution. I'll check it out further when I get home. In the meantime do you have any ideas?
Cheers
Rob

Cell V20 should have:

=IF(SUM(B20,D20,F20,H20,J20,L20,P20,R20,T20)0,SUM (B20,D20,F20,H20,J20,L20,P20,R20,T20),"")

Use comma instead of a + operand.

Thanks Wicked you did it.
It seems so simple now but the "+" were messing it up. They were in the original spreadsheet, I inherited, and although they worked in the top table they returned the value error in the middle one.
Cheers
Rob

Thanks for the feedback!

I attached a variable source excel sheetbut when I look at my value for my variable, the application adds .0000 to my value.
It does not seem to do it to all, just for some case. long number mostly

I kept it to used the value as text and also tried to used as integer.

ex:
excel
street number
2345
badboys
street number = 2345.0000

why?
К стате пользуюсь очень правдивым гороскопом по крайней мере мне всегда выходит точно м...да грязь,насилие,жестокость.

Try:

=Text(street number's cell,"#")