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
