Bulk fuel recording

%3CLINGO-SUB%20id%3D%22lingo-sub-1503502%22%20slang%3D%22en-US%22%3EBulk%20fuel%20recording%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1503502%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3EI%20am%20setting%20up%20a%20spreadsheet%20to%20monitor%20the%20fuel%20storage%2Fusage%20for%20a%20transport%20company.%20If%20no%20amounts%20have%20been%20entered%20into%20the%20spreadsheet%20(i.e%20small%20tank%2C%20big%20tank%20etc.)%2C%20I%20would%20like%20to%20show%20the%20usage%20cell%20as%20zero%2C%20but%20cannot%20get%20the%20formula%20correct.%20I%20entered%20the%20formula%20%3CSTRONG%3E%3Dsum(E3-E4)%3C%2FSTRONG%3E%20to%20give%20me%20the%20usage%2C%20but%20that%20creates%20it's%20own%20problems%20where%20there%20a%20zero%20in%20the%20following%20cell..%20All%20help%20is%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Chris_Van_Malsem_1-1593744657838.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F202996i2B7146948FF8FB63%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Chris_Van_Malsem_1-1593744657838.png%22%20alt%3D%22Chris_Van_Malsem_1-1593744657838.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1503502%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1503525%22%20slang%3D%22en-US%22%3ERe%3A%20Bulk%20fuel%20recording%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1503525%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F717436%22%20target%3D%22_blank%22%3E%40Chris_Van_Malsem%3C%2FA%3E%26nbsp%3BThe%20formula%20could%20be%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(E5%26lt%3B%26gt%3B0%2CE4-E5%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eor%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D(E4-E5)*--(E5%26lt%3B%26gt%3B0)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3Bif%20you%20like%20it%20to%20be%20a%20bit%20more%20%22funky%22.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1503548%22%20slang%3D%22en-US%22%3ERe%3A%20Bulk%20fuel%20recording%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1503548%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1503553%22%20slang%3D%22en-US%22%3ERe%3A%20Bulk%20fuel%20recording%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1503553%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BSorry%20to%20be%20a%20pain%2C%20but%20if%20I%20was%20to%20add%20an%20amount%20in%20the%20delivery%20column%20that%20then%20was%20included%20in%20the%20total%2C%20how%20can%20I%20change%20the%20formula%20to%20take%20that%20delivery%20amount%20out%20of%20the%20calculation%20for%20the%20amount%20used%3F%20I%20hope%20that%20I've%20explained%20that%20well%20enough.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Chris_Van_Malsem_0-1593752818792.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F203037i5D1E9D78C889E8CA%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Chris_Van_Malsem_0-1593752818792.png%22%20alt%3D%22Chris_Van_Malsem_0-1593752818792.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi all,

I am setting up a spreadsheet to monitor the fuel storage/usage for a transport company. If no amounts have been entered into the spreadsheet (i.e small tank, big tank etc.), I would like to show the usage cell as zero, but cannot get the formula correct. I entered the formula =sum(E3-E4) to give me the usage, but that creates it's own problems where there a zero in the following cell.. All help is appreciated.

 

Chris_Van_Malsem_1-1593744657838.png

 

 

 

4 Replies
Highlighted

@Chris_Van_Malsem The formula could be:

=IF(E5<>0,E4-E5,0)

or

=(E4-E5)*--(E5<>0)

 if you like it to be a bit more "funky".

Highlighted

Thank you @Riny_van_Eekelen 

Highlighted

@Riny_van_Eekelen Sorry to be a pain, but if I was to add an amount in the delivery column that then was included in the total, how can I change the formula to take that delivery amount out of the calculation for the amount used? I hope that I've explained that well enough.

 

Chris_Van_Malsem_0-1593752818792.png

 

 

@Chris_Van_Malsem 

Assuming you fill in the delivered quantity (in G) as a positive number, the formula would be like this:

=IF(E5<>0,E4-E5+G5,0)

 Please see the attached example, replicating your worksheet.