Value errors

**Eboof** · 10-05-2010, 05:25 PM

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

**davegugg** · 10-05-2010, 05:56 PM

Just a quick suggestion: you could apply a custom format where if the cell value is 0, the cell's font's color is white. Then no zeros would show up, but you could still do any math on the cells. Would that work for you?

**Eboof** · 10-05-2010, 06:09 PM

Thanks Dave, I think you meant "zeroes would show up..." but would not be visible.UInfortunately I want to work on the data but exclude zeroes. Zeroes will mess up my min and median formulas i.e. for some of the value columns in the body of the table I need to calculate Min and Median and I want to exclude those rows where no data has been submitted.

**davegugg** · 10-06-2010, 11:37 AM

I guess I'm not understanding your problem. I can see in col Z you have a Median and Min of the values in column V, and they appear correct to me. How would you expect them to be different? Can you provide an example of where you are getting wrong or skewed data?

**Eboof** · 10-06-2010, 02:54 PM

Hi Dave,
I'm sorry I probably wasn't very clear. The three tables are three examples. I need to use a table like the middle one (referenced, with no zeroes) because I want to perform the Min, Max and Median on Cols B and F as well.
The problem has been solved by using this formula in V20.

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

Apparently, the "+" sign in front of the cell worked in the top table but not the middle.
Thanks for all your help and sorry for the confusion.
Cheers
Rob