progressive variance/difference

[Marcia_D]

Hi

Apologies if this has been answered anywhere before, but have searched everywhere to find a solution and I am going grey rapidly :-/

I need a formula to give me a progressive difference as data (weights) are being entered. Note some months may be blank due to varying reasons, so only need the difference between those that have been logged and progressively as they are. Hope that makes sense! E.g.

Jan weight 78 / Feb weight 77 therefore difference being -1. should then Mar be entered at say 75 then the difference will then show to be -3. That in itself is not difficult, it is when I have a month where no data is entered that I run into trouble! Where there is one or no weights logged then the value would be null. Prefer not to get into VBA if it can be helped. I may be overthinking it but damned if I can get around it

Thanking you in advance. Really appreciate any help possible

Cheers,
Marcia

 

[Peter_SSs]

Is this what you mean? If not please give some more complete sample data and the expected results.

Book1
	A	B	C
1	Month	Amt	Prog Diff
2	Jan	78
3	Feb	77	-1
4	Mar	75	-3
5	Apr
6	May	70	-8
7	Jun	68	-10
8	Jul
9	Aug
10	Sep	67	-11
11	Oct
12	Nov
13	Dec
Prog Diff

Cell Formulas
Range	Formula
C3:C12	C3	=IF(B3,B3-B$2,"")

 

[Marcia_D]

Hi
Gives the result, but next to each month rather than just in the one cell. I need it to be reported in the one cell only as the data is entered - if that is possible? I have gotten myself rather convoluted with nested ifs, and have gotten myself unstuck doing so

Forgot to mention that it is a quarters data only e.g. only over three months per customer...need to know what the overall weight change is in that quarter where data is given and as it is entered.

 

[Marcia_D]

Example:- noting that I would not have three different tables of data but just the one, with the below demonstrating as each month passes how the result would change

 

[Peter_SSs]

Ok, try this one, copied down.

Also, for the future, you will generally get faster & better help if you can provide sample data in a form that helpers can copy/paste to test with. My signature block below has help on that.

Book1
	A	B	C	D	E
2		Jan	Feb	Mar	Diff
3	Mr A	75	78		3
4	Ms B	75			0
5	Mrs C				0
6	Mr Z		58	44	-14
7	Mr Q	44	65	43	-1
Prog Diff (2)

Cell Formulas
Range	Formula
E3:E7	E3	=IF(COUNT(B3:D3),LOOKUP(1E+100,B3:D3)-INDEX(B3:D3,MATCH(TRUE,INDEX(B3:D3<>"",0),0)),0)

 

[Marcia_D]

Awesome! Thank you so much and I take on board your feedback. Appreciate your patience and expertise

Kind regards,
Marcia

 

[Peter_SSs]

You're welcome.

Note that I first posted an incorrect formula and then edited my post to give the current one so just check that you are using the latest one.