Quote:
Originally Posted by eBoof
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),""))