Adjust dividing formula when a value is zero

[vladimiratanasiu]

Hello!

I have a table, presenting electricity consumption of a client and its variation (+/-) from one month vs. the same one of the previous year. Sometimes, there are months with no consumption (see B21, B22, B26). My formula works fine when two similar months with non-zero values are compared. However, when the first one is zero (e.g. B21, B22, B26) and the next is non-zero (e.g. B34, B35, B39), I need the proper result should be a negative / positive number (E34, E35, E39), but my formula generates 0. How could adjust / change it, in order to get the necessary value?
Thank you!

Book2.xlsx
	A	B	C	D	E
1	Electrictricity consumption			Annual differences (%)
2	Period	Kwh	Value (RON, including VAT)	Consumption (Kwh)
3
4	Jan-22	54.00	36.57
5	Feb-22	66.00	44.70
6	Mar-22	78.00	53.04
7	Apr-22	68.00	46.25
8	May-22	-10.00	-7.12
9	Jun-22	0.00	0.00
10	Jul-22	0.00	0.00
11	Aug-22	49.00	33.32
12	Sep-22	27.00	18.13
13	Oct-22	51.00	34.62
14	Nov-22	0.00	0.00
15	Dec-22	0.00	0.00
16
17	Jan-23	49.97	44.70	-7.46%
18	Feb-23	16.12	53.04	-75.58%
19	Mar-23	106.00	64.98	35.90%
20	Apr-23	96.00	64.67	41.18%
21	May-23	46.00	31.72	-560.00%
22	Jun-23	188.00		0.00%	18800%
23	Jul-23	144.00		0.00%	14400%
24	Aug-23			-100.00%
25	Sep-23			-100.00%
26	Oct-23			-100.00%
27	Nov-23	-4.00		0.00%	-400%
28	Dec-23			0.00%
Sheet1

Cell Formulas
Range	Formula
D17:D28	D17	=IFERROR(-(1-B17/B4),0)

 

[vladimiratanasiu]

I attach a new table, including the updated data according with the cell references from my previous message. Thank you.

Book2.xlsx
	A	B	C	D	E
1	Electrictricity consumption			Annual differences
2	Period	Kwh	Value (RON, including VAT)	Consumption (Kwh)
3	Jan-21	67.82	0.00
4	Feb-21	61.25	104.51
5	Mar-21	59.19	0.00
6	Apr-21	48.36	100.60
7	May-21	49.97	0.00
8	Jun-21	48.36	79.73
9	Jul-21	49.97	0.00
10	Aug-21	16.12	80.09
11	Sep-21	67.00	0.00
12	Oct-21	11.00	63.77
13	Nov-21	44.00	35.97
14	Dec-21	0.00	0.00
15
16	Jan-22	54.00	36.57	-20.38%
17	Feb-22	66.00	44.70	7.76%
18	Mar-22	78.00	53.04	31.78%
19	Apr-22	68.00	46.25	40.61%
20	May-22	-10.00	-7.12	-120.01%
21	Jun-22	0.00	0.00	-100.00%
22	Jul-22	0.00	0.00	-100.00%
23	Aug-22	49.00	33.32	203.97%
24	Sep-22	27.00	18.13	-59.70%
25	Oct-22	51.00	34.62	363.64%
26	Nov-22	0.00	0.00	-100.00%
27	Dec-22	0.00	0.00	0.00%
28
29	Jan-23	49.97	44.70	-7.46%
30	Feb-23	16.12	53.04	-75.58%
31	Mar-23	106.00	64.98	35.90%
32	Apr-23	96.00	64.67	41.18%
33	May-23	46.00	31.72	-560.00%
34	Jun-23	188.00		0.00%	18800%
35	Jul-23	144.00		0.00%	14400%
36	Aug-23			-100.00%
37	Sep-23			-100.00%
38	Oct-23			-100.00%
39	Nov-23	-4.00		0.00%	-400%
40	Dec-23			0.00%
Sheet1

Cell Formulas
Range	Formula
D16:D27,D29:D40	D16	=IFERROR(-(1-B16/B3),0)

 

[Pulsar3000]

I attach a new table, including the updated data according with the cell references from my previous message. Thank you.

Book2.xlsx
	A	B	C	D	E
1	Electrictricity consumption			Annual differences
2	Period	Kwh	Value (RON, including VAT)	Consumption (Kwh)
3	Jan-21	67.82	0.00
4	Feb-21	61.25	104.51
5	Mar-21	59.19	0.00
6	Apr-21	48.36	100.60
7	May-21	49.97	0.00
8	Jun-21	48.36	79.73
9	Jul-21	49.97	0.00
10	Aug-21	16.12	80.09
11	Sep-21	67.00	0.00
12	Oct-21	11.00	63.77
13	Nov-21	44.00	35.97
14	Dec-21	0.00	0.00
15
16	Jan-22	54.00	36.57	-20.38%
17	Feb-22	66.00	44.70	7.76%
18	Mar-22	78.00	53.04	31.78%
19	Apr-22	68.00	46.25	40.61%
20	May-22	-10.00	-7.12	-120.01%
21	Jun-22	0.00	0.00	-100.00%
22	Jul-22	0.00	0.00	-100.00%
23	Aug-22	49.00	33.32	203.97%
24	Sep-22	27.00	18.13	-59.70%
25	Oct-22	51.00	34.62	363.64%
26	Nov-22	0.00	0.00	-100.00%
27	Dec-22	0.00	0.00	0.00%
28
29	Jan-23	49.97	44.70	-7.46%
30	Feb-23	16.12	53.04	-75.58%
31	Mar-23	106.00	64.98	35.90%
32	Apr-23	96.00	64.67	41.18%
33	May-23	46.00	31.72	-560.00%
34	Jun-23	188.00		0.00%	18800%
35	Jul-23	144.00		0.00%	14400%
36	Aug-23			-100.00%
37	Sep-23			-100.00%
38	Oct-23			-100.00%
39	Nov-23	-4.00		0.00%	-400%
40	Dec-23			0.00%
Sheet1

Cell Formulas
Range	Formula
D16:D27,D29:D40	D16	=IFERROR(-(1-B16/B3),0)

Try this...

In your IFERROR formula, replace the zero with a "1*cell in B column".

For example, the formula in D34 would be:

=IFERROR(-(1-B34/B21),1*B34)

Have a question though. Shouldn't these scenarios show 100% instead? After all, when there's electricity consumption in the prior year but not the current year, you show -100% like in cells D36 through D38. Shouldn't the opposite scenario show a positive 100%? If you prefer this instead, replace the 0 with a 1 in your IFERROR formula.

 

[vladimiratanasiu]

Try this...

In your IFERROR formula, replace the zero with a "1*cell in B column".

For example, the formula in D34 would be:

=IFERROR(-(1-B34/B21),1*B34)

Have a question though. Shouldn't these scenarios show 100% instead? After all, when there's electricity consumption in the prior year but not the current year, you show -100% like in cells D36 through D38. Shouldn't the opposite scenario show a positive 100%? If you prefer this instead, replace the 0 with a 1 in your IFERROR formula.

Thank you for the answer and solution to my problem. Regarding your question, the value 100% will indicate a positive consumption. It means that the client used twice as much energy, comparing with the same month from the previous year. But the real consumption is zero, so the difference will be negative(-100%). The same scenario is available conversely too, when the present value is positive and the last one negative.

 

[vladimiratanasiu]

Try this...

In your IFERROR formula, replace the zero with a "1*cell in B column".

For example, the formula in D34 would be:

=IFERROR(-(1-B34/B21),1*B34)

Have a question though. Shouldn't these scenarios show 100% instead? After all, when there's electricity consumption in the prior year but not the current year, you show -100% like in cells D36 through D38. Shouldn't the opposite scenario show a positive 100%? If you prefer this instead, replace the 0 with a 1 in your IFERROR formula.

I have one more question: in cell D3, the result is mathematically correct (-560%). Anyway, in normal terms the real consumption is positive (+560%), comparing the two months. How could I express this special situation in the present formula / a distinct one, so as the final result to be +560% ? Thank you!