Hi all, Pretty newbie question here about excel. Basically I have a table which I am working out a load of percentages and because it isn't all filled in I get the DIV/0! errors or ## everywhere unless I enter some data. What formula can I put to make it blank until I enter date into the relevant boxes. Screenies incoming. Formula for the far right V4 for example is, =AVERAGE(D4:Q4). As you can see in V12 etc, it shows errors if there is nothing in the cells. I've tried googling but they all use different equations to decide what to do, not what i'm trying. Geez I hate maths and formulas! Does anyone know what I should put? Basically, if there is no data I want the cell blank, then when I input data, it shows the correct figures (like it does now). Thanks in advance Pete
Right, so that worked a treat. However, now I have a problem that where I need to use the damn spreadsheet, has an older Excel. That doesn't work with IFERROR and some other things. Needs to save as a 97-2093 workbook, but it gives me warnings about it not working, which it doesn't work properly. What formula can I use to replace the IFERROR I have been using? Also, it seems it will mess up the Cond. Formatting - so when it goes above/below values it won't change colour etc as it should - I asssume that is what will be lost? But it doesn't help much. When I click find it just highlights all the cells with formulas in, which I obviously know are the culprits. But how can I get around this without losing the functions? Anyone know? Cheers
save as the newer version, get anyone who needs to open them to get the office compatibility pack? linky
Tell me if I'm being stupid, but what if you just did this? =IF(SUM($D4:$Q4)=0, 0, AVERAGE($D4:$Q4)) I suggest you use 0 in this formula instead of "" because if you have an entry that's all 0's you still want the correct answer.
You can use IF(ISERROR(... in place of IFERROR, as detailed in the last example here: http://www.techonthenet.com/excel/formulas/iserror.php, for pre-2007 compatibility. Conditional formating might be trickier unless you can test it in the older version of excel. Failing that, read up here: https://support.office.com/en-gb/ar...10d-9380-6e259f3cc933#bmconditionalformatting And make things as simple as possible.
