Variance Calculations

Hello,

Thanks in advance for the help. I have a worksheet that lists account names
in column A. In column B I have sales from January, column C is sales from
February, Column D is data from March… all the way through to December.

I need help calculating variance’s on the months that double.

For example:
Jan $10
Feb $15 (no variance calculated)
March $15 (no variance calculated)
April $20 (the variance here would be $10 because April doubled January)
May $40 (the variance here would be $20 because May doubled April)
June $45 (no variance calculated)
July $10 (no variance calculated)
Aug $75 (no variance calculated – did not double high month)
Sep $135 (the variance here is $95, because it doubled May)
Oct $150 (no variance calculated)
Nov $250 (no variance calculated)
Dec $270 (the variance here is $135, because it doubled September)

I know this is complex but as I watch the board I’m confident that someone
can help me.

Thanks
Jim

And the final result should add all the variances together as a sum.




"Jim" wrote:

Hello,

Thanks in advance for the help. I have a worksheet that lists account names
in column A. In column B I have sales from January, column C is sales from
February, Column D is data from March… all the way through to December.

I need help calculating variance’s on the months that double.

For example:
Jan $10
Feb $15 (no variance calculated)
March $15 (no variance calculated)
April $20 (the variance here would be $10 because April doubled January)
May $40 (the variance here would be $20 because May doubled April)
June $45 (no variance calculated)
July $10 (no variance calculated)
Aug $75 (no variance calculated – did not double high month)
Sep $135 (the variance here is $95, because it doubled May)
Oct $150 (no variance calculated)
Nov $250 (no variance calculated)
Dec $270 (the variance here is $135, because it doubled September)

I know this is complex but as I watch the board I’m confident that someone
can help me.

Thanks
Jim

"Jim" wrote previously:
In column B I have sales from January, column C
is sales from February, Column D is data from March…
all the way through to December.
I need help calculating variance’s on the months that double.

If the B2:M2 contains the Jan-Dec sales figures, and you want to compute the
"variances" (differences) in C2:M2, if any is applicable according to your
rules, then one way to do this is....

Put zero into C2. If you want to hide the zero, use the Custom format
General;-General; (with both semicolons). Then put the following formula
into C3 and copy across through M3:

=IF(C2=2*INDEX($B$2:$M$2,MATCH(9E+307,$B$3:B3)),
C2-INDEX($B$2:$M$2,MATCH(9E+307,$B$3:B3)), "")

How it works.... The MATCH expression returns the index of the last column
in row 3 with a number ("variance"); the INDEX expression returns the
corresponding number (sales) in row 2.


"Jim" wrote:
And the final result should add all the variances
together as a sum.

=SUM(C3:M3)


----- original message -----

"Jim" wrote:
And the final result should add all the variances together as a sum.




"Jim" wrote:

Hello,

Thanks in advance for the help. I have a worksheet that lists account names
in column A. In column B I have sales from January, column C is sales from
February, Column D is data from March… all the way through to December.

I need help calculating variance’s on the months that double.

For example:
Jan $10
Feb $15 (no variance calculated)
March $15 (no variance calculated)
April $20 (the variance here would be $10 because April doubled January)
May $40 (the variance here would be $20 because May doubled April)
June $45 (no variance calculated)
July $10 (no variance calculated)
Aug $75 (no variance calculated – did not double high month)
Sep $135 (the variance here is $95, because it doubled May)
Oct $150 (no variance calculated)
Nov $250 (no variance calculated)
Dec $270 (the variance here is $135, because it doubled September)

I know this is complex but as I watch the board I’m confident that someone
can help me.

Thanks
Jim