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 (empty cell = 0)
Can someone help me I knoow there are many possibilities
IF
IFISBLANK
IFERROR
I need to add it to:
=[@[Current Fuel Costs Pence Per Litre]]/[@[Miles Per Liter]]/100
(located in =D2 or [@Cost Per1 Mile]]
---------- AND ---------
=[@[Cost Per 1 Mile]]*[@[Total Miles]] (it works with the above)
(located in H2 or [@Total Fuel Costs]]
PS why was it always giving me this format [@[Cost Per 1 Mile]] of @ header for first row but now some are giving me shorthand D2 etc for first row, I have messed up a lot of files trying to get it right, don't know if I did something wrong.
And if you can explain the procedure, I appreciate it as to how many (, [, etc need be where so I can't do it going forward, I tried coping simple formulas and pasting my in the sections, but I think due to the type of formula I need to add a ( or something somewhere.
EDIT: I might have found it =IFERROR([@[Current Fuel Costs Pence
Per Litre]]/[@[Miles Per Liter]]/100,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.
