Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#3
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#4
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#5
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#6
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Formula Request | Excel Worksheet Functions | |||
Strange request: Counting cells and percentages | Excel Discussion (Misc queries) | |||
Formula request | Excel Discussion (Misc queries) | |||
Formula help request | Excel Discussion (Misc queries) | |||
formula request | Excel Worksheet Functions |