Calculate only if cell is not empty

H

Harrij

New Member
Joined
Nov 3, 2009
Messages
10
Office Version
  1. 2021
Platform
  1. MacOS
Pfff tried all but I do fail at this simple question. Ik just want to calculate two cells and only want the result if the ONE cell is <>0.
I want to have a result in G8 only if a value in column F <> 0. As you can see I now end up with a result in G9 which I do not want to see.
The calculation is just basic maths example: G8 result cell |. =Sum(F8-E8) and result -345 thats ok and fine. Now I want to have the next calculation
to NOT calculate (or leave result cell empty ) when in this example cell F9 = 0

I guess I need something like =IF(F9<>0,"",F9-E9). where F9 needs to become any cell in column F containing a 0 value.
But this keeps giving an error in the formula..... whose got more brains than me....??? Its all basic but I can't figure it out...



test.xlsx
H
13
Sheet1
 

Attachments

  • CleanShot 2022-08-06 at 22.08.32@2x.png
    CleanShot 2022-08-06 at 22.08.32@2x.png
    80.2 KB · Views: 14

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
Not sure if I understand your question correctly but if you want G9 empty when F9=0 it should be =IF(F9=0,"",F9-E9) that way you get all negative or positive numbers calculated that might show.. Let me know if I misunderstood your question and I can modify.
 
Upvote 0
@Harrij
I think that @Doodle24 has given you the formula that you need but I have shown it again, in action, below as I wanted to give you the hint that with XL2BB you need to actually select the range that you want to show before you click the 'Mini Sheet' button.

22 08 08.xlsm
EFG
7376250-126
836520-345
95170 
Harrij
Cell Formulas
RangeFormula
G7:G9G7=IF(F7=0,"",F7-E7)
 
Upvote 0
Doodle24 said:
Not sure if I understand your question correctly but if you want G9 empty when F9=0 it should be =IF(F9=0,"",F9-E9) that way you get all negative or positive numbers calculated that might show.. Let me know if I misunderstood your question and I can modify.
Click to expand...
Cant get it to work. This is the full sheet attached: Note that as you stated I want column G stating 0 if a corresponding cell in column F=0. Formula can be cell based and not column, I just want the
result of Fx-Ex=Gx and in case of Fx=0 then Gx=0
Error states this can't be done in ways I tried. ( COUNTIF, IFBLANK, IF, SUMIF BLABLA.....NO NO NO ) Still there's loads of brain around fortunately !!!

Energie II.xlsx
ABCDEFGHIJKL
1
2
3
4
5ELEKTRICITEITELEC TOTAAL
6CONTRACT PERIODEJAARMAANDVERSCHILpanelenelec-panelen
7ABoudnieuwAE
820232022SEP3760-376002023
920232022OKT3650-36500
1020232022NOV5170-51700
1120232022DEC5090-50900
1220232023JAN4670-46700
1320232023FEBR3970-397000
1420232023MRT3320-33200
1520232023APRIL3030-30300
1620232023MEI2630-26300
1720232023JUNI2440-24400
1820232023JULI2580-25800
1920232023AUG00000
2020242023SEP000002024
2120242023OKT00000
2220242023NOV00000
2320242023DEC00000
2420242024JAN00000
2520242024FEBR00000
2620242024MRT000000
2720242024APRIL00000
2820242024MEI00000
2920242024JUNI00000
3020242024JULI00000
3120242024AUG00000
3220252024SEP000002025
3320252024OKT00000
3420252024NOV00000
3520252024DEC00000
3620252025JAN00000
3720252025FEBR00000
3820252025MRT000000
3920252025APRIL00000
4020252025MEI00000
4120252025JUNI00000
4220252025JULI00000
4320252025AUG00000
4420262025SEP000002026
4520262025OKT00000
4620262025NOV00000
4720262025DEC00000
4820262026JAN00000
4920262026FEBR00000
5020262026MRT000000
5120262026APRIL00000
5220262026MEI00000
5320262026JUNI00000
5420262026JULI00000
5520262026AUG00000
56
57
58
59
60
61
62
63
64
65
66
67
Sheet1
Cell Formulas
RangeFormula
K13K13=SUM(F8:F55)
K26K26=SUM(G20:G31)
K38K38=SUM(F8:F55)
K50K50=SUM(F8:F55)
E20:E55E20=F8
G8:G55G8=F8-E8
I8:I55I8=F8-H8
 
Upvote 0
Harrij said:
I want column G stating 0 if a corresponding cell in column F=0
Click to expand...
That is slightly different to what you said before
Harrij said:
Now I want to have the next calculation
to NOT calculate (or leave result cell empty ) when in this example cell F9 = 0
Click to expand...
In any case, if we do what you are now asking, your whole column G in your post #4 sample would be 0 since all the values in column F are zero. Is that what you want?
 
Upvote 0
Peter_SSs said:
That is slightly different to what you said before

In any case, if we do what you are now asking, your whole column G in your post #4 sample would be 0 since all the values in column F are zero. Is that what you want?
Click to expand...
Yep, that would be fine as the zero's are not used for graphs. The calculation from top to bottom is the same. Column E and F get updated once a month when new info is ready, and stay ) till actual numbers are known.
 
Upvote 0
So the formula given before is basically it isn't it? Only differences are that it now seems to start on row 8 and you want 0 returned instead of "".

22 08 08.xlsm
EFG
6VERSCHIL
7oudnieuwAE
837600
936500
1051700
1150900
1246700
1339700
1433200
1530300
1626300
1724400
1825800
19000
20000
21000
22000
23000
24000
25000
26000
27000
28000
29000
30000
31000
32000
33000
34000
35000
36000
37000
38000
39000
40000
41000
42000
43000
44000
45000
46000
47000
48000
49000
50000
51000
52000
53000
54000
55000
Harrij
Cell Formulas
RangeFormula
G8:G55G8=IF(F8=0,0,F8-E8)
 
Upvote 0
Peter_SSs said:
So the formula given before is basically it isn't it? Only differences are that it now seems to start on row 8 and you want 0 returned instead of "".

22 08 08.xlsm
EFG
6VERSCHIL
7oudnieuwAE
837600
936500
1051700
1150900
1246700
1339700
1433200
1530300
1626300
1724400
1825800
19000
20000
21000
22000
23000
24000
25000
26000
27000
28000
29000
30000
31000
32000
33000
34000
35000
36000
37000
38000
39000
40000
41000
42000
43000
44000
45000
46000
47000
48000
49000
50000
51000
52000
53000
54000
55000
Harrij
Cell Formulas
RangeFormula
G8:G55G8=IF(F8=0,0,F8-E8)
Click to expand...

Strange, I'm on a iMac and whenever I fill this formula in cell F8 or G8 I get an error. Its just a clean sheet without additional sheets.
 
Upvote 0
Harrij said:
I'm on a iMac
Click to expand...
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)


Harrij said:
whenever I fill this formula in cell F8 or G8 I get an error.
Click to expand...
It goes in G8, not F8
Can you show us with XL2BB?
 
Upvote 0
Peter_SSs said:
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)



It goes in G8, not F8
Can you show us with XL2BB?
Click to expand...
Updated my account. Can't do any selection with XL2BB as the error pops up, when entered the formula in G8 cell. Just paste your formula in G8 and the error appears. Well it does on my Mac anyway...
-
Energie II.xlsx
ABCDEFGHIJKL
1
2
3
4ELEKTRICITEIT
5ELEC TOTAAL
6CONTRACT PERIODEJAARMAANDVERSCHILpanelenelec-panelen
7oudnieuw
820232022SEP3760-376002023
920232022OKT3650-36500
1020232022NOV5170-51700
1120232022DEC5090-50900
1220232023JAN4670-46700
1320232023FEBR3970-397000
1420232023MRT3320-33200
1520232023APRIL3030-30300
1620232023MEI2630-26300
1720232023JUNI2440-24400
1820232023JULI2580-25800
1920232023AUG00000
2020242023SEP000002024
2120242023OKT00000
2220242023NOV00000
2320242023DEC00000
2420242024JAN00000
2520242024FEBR00000
2620242024MRT000000
2720242024APRIL00000
2820242024MEI00000
2920242024JUNI00000
3020242024JULI00000
3120242024AUG00000
3220252024SEP000002025
3320252024OKT00000
3420252024NOV00000
3520252024DEC00000
3620252025JAN00000
3720252025FEBR00000
3820252025MRT000000
3920252025APRIL00000
4020252025MEI00000
4120252025JUNI00000
4220252025JULI00000
4320252025AUG00000
4420262025SEP000002026
4520262025OKT00000
4620262025NOV00000
4720262025DEC00000
4820262026JAN00000
4920262026FEBR00000
5020262026MRT000000
5120262026APRIL00000
5220262026MEI00000
5320262026JUNI00000
5420262026JULI00000
5520262026AUG00000
56
57
Electriciteit
Cell Formulas
RangeFormula
K13K13=SUM(F8:F55)
K26K26=SUM(G20:G31)
K38K38=SUM(F8:F55)
K50K50=SUM(F8:F55)
E20:E55E20=F8
G8:G55G8=F8-E8
I8:I55I8=F8-H8
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Timesheet Calculation
Replies
7
Views
816
jorismoerings
J
J
  • Solved
Can someone explain on how this strange excel formula works exactly?
Replies
3
Views
334
jvs411
J
A
  • Solved
Calculating income tax in reverse
Replies
9
Views
279
Dave Patton
D
E
  • Question
Formula Query (University Results Spreadsheet)
Replies
1
Views
105
SanjayGMusafir
SanjayGMusafir
L
  • Solved
Linking Data on Different Tab and Showing Blank Values Instead of Zeroes
Replies
3
Views
82
Fluff
Fluff

Forum statistics

Threads
1,215,473
Messages
6,125,018
Members
449,203
Latest member
tungnmqn90

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top