Forum Discussion
Help combine empty cell isblank with long formula (#DIV/0!)
Hi Stuck again Trying so many variations, I managed to do it yesterday but forgot to save it, not managing to figure out how I did it. So the dreaded #DIV/0! numbers can't be divided by 0...
So yes there are many ways to get excel to do what you want and although all may work, sometimes some are better than others (and in other situations those same 'others' may be better).
So for example you can easily add IFERROR( ...formula... , "") to get rid of those #DIV/0! error messages but the problem is that you will not get ANY error messages so if there is a DIFFERENT error you wont see that either. So I would recommend something more like =IF( [@[Miles Per Liter]] > 0 , [@[Current Fuel Costs Pence Per Litre]]/[@[Miles Per Liter]]/100 , "") so that it will only do that formula IF that denominator is > 0.
As for what that format is, it is structured table format. Basically the [Miles Per Liter] says to look at the column of this table with the header called 'Miles Per Liter'. The @ symbol means look at the value in THIS ROW. So for example [@MPG] would be the value in the MPG column in THIS row. now you might notice I didn't use that extra set of [] but that is because the header name doesn't have any extra spaces. So Miles Per Liter needs that extra set of [] to clarify the name because of those spaces. If you have a formula that is NOT in this table (on another sheet or just on this sheet but outside the table boundaries) then it will require the Table Name so if you didn't Name the table otherwise it is the first table it will default to 'Table1' so the reference if you wanted to sum all the miles might be =SUM(Table1[Miles One Way]). Notice I did NOT use an @ symbol so it refers to the WHOLE column of data and sums it all up.
No you might ask WHY. Basically this table references are more efficient. There are many reasons but a simple example is the SUM I did above could also be achieved using =SUM(E:E) (assuming that is column E) but then Excel will be looking down the ENTIRE column of E and if you start having many complicated formula it could add up and cause Excel to be slow. But using =SUM(Table1[Miles One Way]) means Excel will only look at the values in the table.
So for example you can easily add IFERROR( ...formula... , "") to get rid of those #DIV/0! error messages but the problem is that you will not get ANY error messages so if there is a DIFFERENT error you wont see that either. So I would recommend something more like =IF( [@[Miles Per Liter]] > 0 , [@[Current Fuel Costs Pence Per Litre]]/[@[Miles Per Liter]]/100 , "") so that it will only do that formula IF that denominator is > 0.
As for what that format is, it is structured table format. Basically the [Miles Per Liter] says to look at the column of this table with the header called 'Miles Per Liter'. The @ symbol means look at the value in THIS ROW. So for example [@MPG] would be the value in the MPG column in THIS row. now you might notice I didn't use that extra set of [] but that is because the header name doesn't have any extra spaces. So Miles Per Liter needs that extra set of [] to clarify the name because of those spaces. If you have a formula that is NOT in this table (on another sheet or just on this sheet but outside the table boundaries) then it will require the Table Name so if you didn't Name the table otherwise it is the first table it will default to 'Table1' so the reference if you wanted to sum all the miles might be =SUM(Table1[Miles One Way]). Notice I did NOT use an @ symbol so it refers to the WHOLE column of data and sums it all up.
No you might ask WHY. Basically this table references are more efficient. There are many reasons but a simple example is the SUM I did above could also be achieved using =SUM(E:E) (assuming that is column E) but then Excel will be looking down the ENTIRE column of E and if you start having many complicated formula it could add up and cause Excel to be slow. But using =SUM(Table1[Miles One Way]) means Excel will only look at the values in the table.
mtarler the combination given gives an error and won't work unfortunately I'm trying to read through to see if I can spot the issue
- If you can attach the workbook or give a link to it, I can see what the issue is
mtarler I thought I added a screenshot why isn't this letting me add a screenshot to my replies, screenshot had the exact error
- so based on the format of the formula you had/were trying I assume that it is a Table format, but that is the 1st thing to confirm. Next is to check which if any of the references Excel recognizes and see which it doesn't recognize. Just looking at the name they look correct but a double space vs a single space or many other things like that can be really hard to spot. So click on the formula in the edit bar/window and Excel should highlight in color the cell references it recognizes. It is also easier to click in the cell you want to reference and let Excel add that reference instead of manually typing it. For example you could type
=IF(
then click on the corresponding cell in the 'Miles Per Liter' column and Excel should insert that correct reference into the formula. Then you know the reference is recognized.