I am stuck with working out a formula for the following.
I need to sum H3:H64, but the some I was using showed the result as 100% if no data was in the cell and I want it to display blank if there is no value in either of the cells?
Where am I going wrong please.
The sum that displays 100% is this =100-SUM(H3:H64)/62 and I just nothing in it as I say if the value is blank
I tried this -
=SUM(H3:H64)/62(IF="0""")
But it was a no go
Last edited by abb16ott; 05-01-2010 at 01:53 AM.
To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.
If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.
Doing this will ensure you get the result you need!
Thanks Dave - Here we go.
Last edited by davesexcel; 04-30-2010 at 10:19 PM.
You should attach a simple workbook example.
Read Post 2,
You should show what your original layout looks like and what the desired result should be.
Keep it Simple.
No unwanted sheets should be included in your example.
Sorry about that, is this ok?
Hi,
If I understand what you want, I think this is what you are looking for. It will calculate the percentage according to the number of cells that have data.
=100-(SUM(H3:H64)/(62-COUNTBLANK(H3:H64)))
abousetta
Hi,Originally Posted by abousetta
I gave that a whirl but the cell shows #DIV/0 ?
Any idea what I can do please?
Thanks
That's because all the cells are currently empty.
Try this, it will give you a 0 when all the cells from H3:H64 are empty.
=IFERROR(100-(SUM(H3:H64)/(62-COUNTBLANK(H3:H64))),0)
abousetta
Well now I am getting #NAME? - maybe it can't be done
Do you have Excel 2007 or an earlier version? I came save your excel sheet as Excel 2003 and it warned me that this will happen. Its a compatibility issue.
abousetta
Try this formula. It seems to work fine in Excel 2003
=IF(COUNTBLANK(H3:H64)=62,0,100-(SUM(H3:H64)/(62-COUNTBLANK(H3:H64))))
abousetta
Yes I have Excel 2003.
I have just given this one a go and - hey presto it appears to be working
Thank you for all that hard work.
Regards.
Glad I could help. Just remember that if you ever change the range, to update the formula because it uses the magic number 62 is several places in the formula.
If you are satisfied with the results, then please mark the thread as [SOLVED].
abousetta
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks