Average Forumula

[ScottishSteve]

Hi folks,

I'm looking for some formula help in Excel.

Basically I have a set of figures as follows:

Bob Date 1 Jan 2 Jan 3 Jan
Value 1 10 10 10
Value 2 5 5 5

I want a formula which figures out the average percentage of value 2
compared to value 1 - i.e. Value 2 for 1st to 3rd Jan, divided by Value 1 for
1st to 3rd Jan.

However, the formula would have to take into account some days will have no
entries in either Value, and that sometimes value 2 will be 0, even when
there is an entry in Value 1 for the same date. i.e.

Bob Date 1 Jan 2 Jan 3 Jan
Value 1 10 10 10
Value 2 5 0 5

The forumula I have just now would give me the same result for both of these
examples - 50%, when the 2nd example should be 33%.

Thanks to anyone who can help me with this.

Steve

 

[Fred Smith]

It would help if you showed us the formula you are currently using.

Assuming your values are in a1:c2, don't you want:
=sum(a2:c2)/sum(a1:c1)
Format as percent.

Regards,
Fred

 

[ScottishSteve]

Thanks Fred.

The formula I have in so far looks like this:
=SUM(D6:R6+D10:R10)/(D5:R5+D9:R9)

As I have 2 rows of data for each value I am measuring.

Basically I am trying to measure, the percentage of times that Value 1 is
done, is Value 2 also completed.

Thanks again for your help.

 

[ScottishSteve]

Actually, scratch that. I've figured out what I was doing wrong. Thanks for
the help though!

Stevie

 

[Fred Smith]

In case it helps for future postings, here are some useful tips to allow you
to get quicker, more accurate answers to your questions:

* The formula you posted results in a #Value error in Excel. So either you
neglected to mention this, which won't get your problem solved, or you typed
in the formula. Never type a formula in your message -- always cut and paste
it. That way, responders will know exactly what formula you have.
* To eliminate the #Value error, you need to formulate it like this:
=SUM(D6:R6,D10:R10)/SUM(D5:R5,D9:R9)
* You need to be clear about what solution you are looking for. In your
first post, you asked for "average percentage of value 2 compare to value
1". In this post, you asked for "done" versus "completed".
* You need to defined terms which may be clear to you, but aren't to other
people. For example, it's not clear what you mean by "done" versus
"completed".

Hope this helps,
Fred