You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Petrol Spreadsheet
- Thread starter cyliyu
- Start date
Peter Bartholomew
Well-Known Member
Firstly, I tend to use a table structured references for gathering information of this kind. This is because I can read a formula like
= [@LPumped1]+[@LPumped2]
and have a fair idea what is going on and, hence, be reasonably confident that the correct references have been selected. Sure
=E8 + L8
is shorter but it lacks meaning, in this case, conceals a deliberate error. The formula
= [@LPumped1]+[@Mileage2]
would have made such 'fat-finger' errors more obvious.
A second trick that I use is to shorten
= IF( [@Date]="", "", (1-[@[%Disc1]]) * [@LPumped1] * [@PerLCost1])
by using the fact that a date will be processed as TRUE and a missing date will be treated as FALSE. This allows you test for the active condition and leave the fallback to the second part of the statement
= IF( [@Date], (1-[@[%Disc1]]) * [@LPumped1] * [@PerLCost1], 0 )
Once one gets to the right of your calculated table, you seem to disappear down innumerable rabbit holes of IF clauses. Several of the formulas will evaluate perfectly well even when referencing zeros (better than null strings), so it is possible to work with fewer calculation branches. Tests are needed to trap 0/0, #DIV0! errors and you might decide to use a custom format (or a sheet setting) to avoid displaying non-significant zeros.
Ultimately though, you need to be happy working with your spreadsheet solution!
= [@LPumped1]+[@LPumped2]
and have a fair idea what is going on and, hence, be reasonably confident that the correct references have been selected. Sure
=E8 + L8
is shorter but it lacks meaning, in this case, conceals a deliberate error. The formula
= [@LPumped1]+[@Mileage2]
would have made such 'fat-finger' errors more obvious.
A second trick that I use is to shorten
= IF( [@Date]="", "", (1-[@[%Disc1]]) * [@LPumped1] * [@PerLCost1])
by using the fact that a date will be processed as TRUE and a missing date will be treated as FALSE. This allows you test for the active condition and leave the fallback to the second part of the statement
= IF( [@Date], (1-[@[%Disc1]]) * [@LPumped1] * [@PerLCost1], 0 )
Once one gets to the right of your calculated table, you seem to disappear down innumerable rabbit holes of IF clauses. Several of the formulas will evaluate perfectly well even when referencing zeros (better than null strings), so it is possible to work with fewer calculation branches. Tests are needed to trap 0/0, #DIV0! errors and you might decide to use a custom format (or a sheet setting) to avoid displaying non-significant zeros.
Ultimately though, you need to be happy working with your spreadsheet solution!
Looks good here. All on one sheet - easy to enter - easy to review.
Is there anything you don't like about your creation?
I used too many If(C2="","".....to check empty cell and to avoid formula error such as #div/0.
Last edited:
A second trick that I use is to shorten
= IF( [@Date]="", "", (1-[@[%Disc1]]) * [@LPumped1] * [@PerLCost1])
by using the fact that a date will be processed as TRUE and a missing date will be treated as FALSE. This allows you test for the active condition and leave the fallback to the second part of the statement
= IF( [@Date], (1-[@[%Disc1]]) * [@LPumped1] * [@PerLCost1], 0 )
Thanks, Peter.
This help!