Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
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 |