Strange Formula Request
 

Hi. I have no idea how to construct this formula. It could be in vb, but
doesn't have to be. Below is a database dump from a timesheet program.
What I need to do is populate the % of Total column as shown below.

For users in states OTHER than CA, we can ignore.
For CA entries, the program creates a total line for me. The data is always
sorted by user, then by state. So CA data is always lumped together per
user with the ca_total directly below the last CA entry. How can I formuate
excel to know that jblow has 2 CA entries totaling 98, and the % of the
total for each line is 64% and 36%??

Any help would be greatly appreciated! Thank you!!!

user State Total % of Total
jblow AL 28
jblow CA 63 64%
jblow CA 35 36%
jblow ca_total 98
bdole AK 18
bdole CA 38 53%
bdole CA 34 47%
bdole ca_total 72

Strange Formula Request
 

You can't use sumif, because you only want to include the rows between the
last subtotal and the current one, i.e. you don't know what the range
reference should be. I think this will require VBA.


On Tue, 12 Oct 2004 23:41:55 -0400, "Steph" wrote:

Hi. I have no idea how to construct this formula. It could be in vb, but
doesn't have to be. Below is a database dump from a timesheet program.
What I need to do is populate the % of Total column as shown below.

For users in states OTHER than CA, we can ignore.
For CA entries, the program creates a total line for me. The data is always
sorted by user, then by state. So CA data is always lumped together per
user with the ca_total directly below the last CA entry. How can I formuate
excel to know that jblow has 2 CA entries totaling 98, and the % of the
total for each line is 64% and 36%??

Any help would be greatly appreciated! Thank you!!!

user State Total % of Total
jblow AL 28
jblow CA 63 64%
jblow CA 35 36%
jblow ca_total 98
bdole AK 18
bdole CA 38 53%
bdole CA 34 47%
bdole ca_total 72

Strange Formula Request
 

OTOH, maybe you CAN do it without VBA. You know the name, so

=IF(B2<"CA","",C2/SUMPRODUCT(--(A$2:A$200=A2),--(B$2:B$200="CA"),C$2:C$200))


On Tue, 12 Oct 2004 23:41:55 -0400, "Steph" wrote:

Hi. I have no idea how to construct this formula. It could be in vb, but
doesn't have to be. Below is a database dump from a timesheet program.
What I need to do is populate the % of Total column as shown below.

For users in states OTHER than CA, we can ignore.
For CA entries, the program creates a total line for me. The data is always
sorted by user, then by state. So CA data is always lumped together per
user with the ca_total directly below the last CA entry. How can I formuate
excel to know that jblow has 2 CA entries totaling 98, and the % of the
total for each line is 64% and 36%??

Any help would be greatly appreciated! Thank you!!!

user State Total % of Total
jblow AL 28
jblow CA 63 64%
jblow CA 35 36%
jblow ca_total 98
bdole AK 18
bdole CA 38 53%
bdole CA 34 47%
bdole ca_total 72

Strange Formula Request
 

Steph,

In next free column, add this formula to B1 and copy down

=IF(B1="CA",C1/SUMPRODUCT(--($A$1:$A$100=A1),--($B$1:$B$100="CA"),$C$1:$C$10
0),"")

form at as percentage

--

HTH

RP

"Steph" wrote in message
...
Hi. I have no idea how to construct this formula. It could be in vb, but
doesn't have to be. Below is a database dump from a timesheet program.
What I need to do is populate the % of Total column as shown below.

For users in states OTHER than CA, we can ignore.
For CA entries, the program creates a total line for me. The data is
always
sorted by user, then by state. So CA data is always lumped together per
user with the ca_total directly below the last CA entry. How can I
formuate
excel to know that jblow has 2 CA entries totaling 98, and the % of the
total for each line is 64% and 36%??

Any help would be greatly appreciated! Thank you!!!

user State Total % of Total
jblow AL 28
jblow CA 63 64%
jblow CA 35 36%
jblow ca_total 98
bdole AK 18
bdole CA 38 53%
bdole CA 34 47%
bdole ca_total 72

Strange Formula Request
 

Thanks Myrna and Bob!!

"Steph" wrote in message
...
Hi. I have no idea how to construct this formula. It could be in vb, but
doesn't have to be. Below is a database dump from a timesheet program.
What I need to do is populate the % of Total column as shown below.

For users in states OTHER than CA, we can ignore.
For CA entries, the program creates a total line for me. The data is
always
sorted by user, then by state. So CA data is always lumped together per
user with the ca_total directly below the last CA entry. How can I
formuate
excel to know that jblow has 2 CA entries totaling 98, and the % of the
total for each line is 64% and 36%??

Any help would be greatly appreciated! Thank you!!!

user State Total % of Total
jblow AL 28
jblow CA 63 64%
jblow CA 35 36%
jblow ca_total 98
bdole AK 18
bdole CA 38 53%
bdole CA 34 47%
bdole ca_total 72

Strange Formula Request
 

Rather than committing to maintain an excel-based timesheet
application for the rest of your life, why not install the free one
from http://journyx.com/newsg?
It's web-based, tracks time, expenses and mileage - automates payroll,
billing and project management, and it's free.

Myrna Larson wrote in message . ..
OTOH, maybe you CAN do it without VBA. You know the name, so

=IF(B2<"CA","",C2/SUMPRODUCT(--(A$2:A$200=A2),--(B$2:B$200="CA"),C$2:C$200))


On Tue, 12 Oct 2004 23:41:55 -0400, "Steph" wrote:

Hi. I have no idea how to construct this formula. It could be in vb, but
doesn't have to be. Below is a database dump from a timesheet program.
What I need to do is populate the % of Total column as shown below.

For users in states OTHER than CA, we can ignore.
For CA entries, the program creates a total line for me. The data is always
sorted by user, then by state. So CA data is always lumped together per
user with the ca_total directly below the last CA entry. How can I formuate
excel to know that jblow has 2 CA entries totaling 98, and the % of the
total for each line is 64% and 36%??

Any help would be greatly appreciated! Thank you!!!

user State Total % of Total
jblow AL 28
jblow CA 63 64%
jblow CA 35 36%
jblow ca_total 98
bdole AK 18
bdole CA 38 53%
bdole CA 34 47%
bdole ca_total 72