Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average Forumula
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average Forumula
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average Forumula
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. "Fred Smith" wrote: 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" wrote in message ... 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 . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average Forumula
Actually, scratch that. I've figured out what I was doing wrong. Thanks for
the help though! Stevie "ScottishSteve" wrote: 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. "Fred Smith" wrote: 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" wrote in message ... 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 . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average Forumula
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 "ScottishSteve" wrote in message ... Actually, scratch that. I've figured out what I was doing wrong. Thanks for the help though! Stevie "ScottishSteve" wrote: 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. "Fred Smith" wrote: 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" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to another forumula? | Excel Discussion (Misc queries) | |||
Need help with a look up forumula I haven't seen before | Excel Worksheet Functions | |||
I Need a forumula or VBA code | Excel Discussion (Misc queries) | |||
Need help making a forumula!!!! | Excel Discussion (Misc queries) | |||
Help with FORUMULA | Excel Discussion (Misc queries) |