# Determining total variance between positive and negative numbers

1. ## Determining total variance between positive and negative numbers

Hi, Can anyone point me in the right direction please?

I have a column of data that lists the variance between scheduled time for a task and actual time taken. I need a summary cell that gives the total variance for all cells regardless of whether it was over or below schedule. for example, in the column below, I need a formula that will return 8.29 as the difference between scheduled and actual time over the 6 days:
-0.38
0.61
-2.87
3.45
-0.90
-0.08

thank you!

2. ## Re: Determining total variance between positive and negative numbers

Use:

``Please Login or Register  to view this content.``
entered with Ctrl-Shift-Enter.

Cheers,

3. ## Re: Determining total variance between positive and negative numbers

Wow! I had no idea about ABS or CTRL+SHIFT+ENTER. Thanks so much. I have an additional challenge for you. That works beautifully if the data is in adjacent rows, however, due to formatting I have to retain, there are merged cells in between the rows. My formula would be something like
{=SUM(ABS(A1:A6,A8-A12,A14-A20))} and it doesn't seem to like it. Is there a way to make it work?

4. ## Re: Determining total variance between positive and negative numbers

Try breaking up the ABS for each array:

=SUM(ABS(A1:A6),ABS(A8:A12),ABS(A14:A20))

Then, CTRL+SHIFT+ENTER

5. ## Re: Determining total variance between positive and negative numbers

maybe
{=SUM(ABS(CHOOSE({1,2,3},A1:A2,A7:A8,A12:A13)))} as an aside

=SUMPRODUCT(ABS(A1:A6)) would have worked
so would
=SUM(SUMPRODUCT(ABS(A1:A2)),SUMPRODUCT(ABS(A3:A4)),SUMPRODUCT(ABS(A5:A6)))
or even
=SUMPRODUCT(ABS(A1:A2)+ABS(A3:A4)+ABS(A5:A6))

6. ## Re: Determining total variance between positive and negative numbers

Perfect! Thank you!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1