Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a Sum but involves Text & multiple functions
Hi - Trying to turn a simple informational table into a math question here.
The initial table includes the following which were initially handentered: (First row headings) col A-G NameOfPayer Check# MembershipAmount MemberName LlamaRegistered@$10ea LlamaEntered@$5ea CheckTotal (2nd row data) Thompson 2214 $30.00 Thompson Denali Denali $45.00 (3rd row data) Hester 345 blankcell blankcell blankcell Tigger,Blackie $10.00 Now the table is getting large enough, that the trial manager wants to calculate the totals after he puts in the rest of the data as a double check to what the check should have been made out for. I need to come up with that formula that generates the total as the data is entered. Some basic facts: Membership is always $30 per person, a lifetime Llama registration is $10 so they don't have to be registered every time they enter a pack trial, trial entry fees are $5 per animal every time, & multiple animals can be entered in a single trial. The total(G2) needs to be the sum of C2 plus E2 plus F2. E2 equals $10 x number of animals recorded in that cell and F2 equals $5 x number of animals recorded in that cell; animal names are separated by commas. Any ideas? Thanks -- Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a Sum but involves Text & multiple functions
Perhaps:
=C2+SUMPRODUCT({10,5},--(E2:F2<""),LEN(E2:F2)-LEN(SUBSTITUTE(E2:F2,",",""))+1) "v!v" wrote: Hi - Trying to turn a simple informational table into a math question here. The initial table includes the following which were initially handentered: (First row headings) col A-G NameOfPayer Check# MembershipAmount MemberName LlamaRegistered@$10ea LlamaEntered@$5ea CheckTotal (2nd row data) Thompson 2214 $30.00 Thompson Denali Denali $45.00 (3rd row data) Hester 345 blankcell blankcell blankcell Tigger,Blackie $10.00 Now the table is getting large enough, that the trial manager wants to calculate the totals after he puts in the rest of the data as a double check to what the check should have been made out for. I need to come up with that formula that generates the total as the data is entered. Some basic facts: Membership is always $30 per person, a lifetime Llama registration is $10 so they don't have to be registered every time they enter a pack trial, trial entry fees are $5 per animal every time, & multiple animals can be entered in a single trial. The total(G2) needs to be the sum of C2 plus E2 plus F2. E2 equals $10 x number of animals recorded in that cell and F2 equals $5 x number of animals recorded in that cell; animal names are separated by commas. Any ideas? Thanks -- Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a Sum but involves Text & multiple functions
Thanks - that seems to solve the problem but I want to understand the steps
as I am unfamiliar with (most) of the solution. Can you walk me thru it? Here's how I interpret your solution: 1) Determine if the cells E2 thru F2 are empty or equal to nothing (E2:F2<""). if it's true, then return nothing, if it's false and something is there, then you tackle the LEN section. 2) After looking up LEN, I see it refers to length of a string. So you look in cells E2 thru F2, & see the animal names there. 3) If there are animal names, you substitute a double quote mark for each name reducing the length of the string to one character - the ". 4) with the --, you change the trues and falses to 1 & 0, multiple the number of 1's if any within E2 by 10 and the number of 1's within F2 by 5. 5) then comes the SUMPRODUCT adding up step 4. 6) Add that value to C2 and you have the answer. But I don't get the +1 and aren't really sure what the math is between steps 2&4 assuming they are generally right. Please explain further. Take your time answering as I'll be on a business trip for few days. I am appreciative of your efforts. Thanks, v!v Thanks "JMB" wrote: Perhaps: =C2+SUMPRODUCT({10,5},--(E2:F2<""),LEN(E2:F2)-LEN(SUBSTITUTE(E2:F2,",",""))+1) "v!v" wrote: Hi - Trying to turn a simple informational table into a math question here. The initial table includes the following which were initially handentered: (First row headings) col A-G NameOfPayer Check# MembershipAmount MemberName LlamaRegistered@$10ea LlamaEntered@$5ea CheckTotal (2nd row data) Thompson 2214 $30.00 Thompson Denali Denali $45.00 (3rd row data) Hester 345 blankcell blankcell blankcell Tigger,Blackie $10.00 Now the table is getting large enough, that the trial manager wants to calculate the totals after he puts in the rest of the data as a double check to what the check should have been made out for. I need to come up with that formula that generates the total as the data is entered. Some basic facts: Membership is always $30 per person, a lifetime Llama registration is $10 so they don't have to be registered every time they enter a pack trial, trial entry fees are $5 per animal every time, & multiple animals can be entered in a single trial. The total(G2) needs to be the sum of C2 plus E2 plus F2. E2 equals $10 x number of animals recorded in that cell and F2 equals $5 x number of animals recorded in that cell; animal names are separated by commas. Any ideas? Thanks -- Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a Sum but involves Text & multiple functions
this part
LEN(E2:F2)-LEN(SUBSTITUTE(E2:F2,",",""))+1 tells you how many animal names are in the string by taking the lengths of the original strings and subtracting the lengths of the strings with the commas removed (using substitute to remove the commas). So "lion, tiger" evaluates to Len("Lion, Tiger")-Len("Lion Tiger")+1 11-10+1 = 2 The +1 is due to the assumption that if there is one comma, there are two animals (and so on). However, if the cell is blank the formula will incorrectly evaluate to 1. Therefore we need --(E2:F2<"") to ensure there is actually something in the cell. As you noted it will return a 1 or 0 and gets multiplied with the results of the Len function above. These results get mulitplied by 10 and 5 respectively. So if you have E F Lion, Tiger <blank The Len function will return 2 and 1. The E2:F2<"" will return 1 and 0. And the 10, 5 is a constant. E F 2 1 1 0 10 5 The product of each column is 20 and 0, which gets added together to give the answer of 20. There is a detailed discussion of sumproduct he http://xldynamic.com/source/xld.SUMPRODUCT.html "v!v" wrote: Thanks - that seems to solve the problem but I want to understand the steps as I am unfamiliar with (most) of the solution. Can you walk me thru it? Here's how I interpret your solution: 1) Determine if the cells E2 thru F2 are empty or equal to nothing (E2:F2<""). if it's true, then return nothing, if it's false and something is there, then you tackle the LEN section. 2) After looking up LEN, I see it refers to length of a string. So you look in cells E2 thru F2, & see the animal names there. 3) If there are animal names, you substitute a double quote mark for each name reducing the length of the string to one character - the ". 4) with the --, you change the trues and falses to 1 & 0, multiple the number of 1's if any within E2 by 10 and the number of 1's within F2 by 5. 5) then comes the SUMPRODUCT adding up step 4. 6) Add that value to C2 and you have the answer. But I don't get the +1 and aren't really sure what the math is between steps 2&4 assuming they are generally right. Please explain further. Take your time answering as I'll be on a business trip for few days. I am appreciative of your efforts. Thanks, v!v Thanks "JMB" wrote: Perhaps: =C2+SUMPRODUCT({10,5},--(E2:F2<""),LEN(E2:F2)-LEN(SUBSTITUTE(E2:F2,",",""))+1) "v!v" wrote: Hi - Trying to turn a simple informational table into a math question here. The initial table includes the following which were initially handentered: (First row headings) col A-G NameOfPayer Check# MembershipAmount MemberName LlamaRegistered@$10ea LlamaEntered@$5ea CheckTotal (2nd row data) Thompson 2214 $30.00 Thompson Denali Denali $45.00 (3rd row data) Hester 345 blankcell blankcell blankcell Tigger,Blackie $10.00 Now the table is getting large enough, that the trial manager wants to calculate the totals after he puts in the rest of the data as a double check to what the check should have been made out for. I need to come up with that formula that generates the total as the data is entered. Some basic facts: Membership is always $30 per person, a lifetime Llama registration is $10 so they don't have to be registered every time they enter a pack trial, trial entry fees are $5 per animal every time, & multiple animals can be entered in a single trial. The total(G2) needs to be the sum of C2 plus E2 plus F2. E2 equals $10 x number of animals recorded in that cell and F2 equals $5 x number of animals recorded in that cell; animal names are separated by commas. Any ideas? Thanks -- Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a Sum but involves Text & multiple functions
Thanks - this explanation was very helpful as is the xldynamic.com site.
v!v -- Thanks "JMB" wrote: this part LEN(E2:F2)-LEN(SUBSTITUTE(E2:F2,",",""))+1 tells you how many animal names are in the string by taking the lengths of the original strings and subtracting the lengths of the strings with the commas removed (using substitute to remove the commas). So "lion, tiger" evaluates to Len("Lion, Tiger")-Len("Lion Tiger")+1 11-10+1 = 2 The +1 is due to the assumption that if there is one comma, there are two animals (and so on). However, if the cell is blank the formula will incorrectly evaluate to 1. Therefore we need --(E2:F2<"") to ensure there is actually something in the cell. As you noted it will return a 1 or 0 and gets multiplied with the results of the Len function above. These results get mulitplied by 10 and 5 respectively. So if you have E F Lion, Tiger <blank The Len function will return 2 and 1. The E2:F2<"" will return 1 and 0. And the 10, 5 is a constant. E F 2 1 1 0 10 5 The product of each column is 20 and 0, which gets added together to give the answer of 20. There is a detailed discussion of sumproduct he http://xldynamic.com/source/xld.SUMPRODUCT.html "v!v" wrote: Thanks - that seems to solve the problem but I want to understand the steps as I am unfamiliar with (most) of the solution. Can you walk me thru it? Here's how I interpret your solution: 1) Determine if the cells E2 thru F2 are empty or equal to nothing (E2:F2<""). if it's true, then return nothing, if it's false and something is there, then you tackle the LEN section. 2) After looking up LEN, I see it refers to length of a string. So you look in cells E2 thru F2, & see the animal names there. 3) If there are animal names, you substitute a double quote mark for each name reducing the length of the string to one character - the ". 4) with the --, you change the trues and falses to 1 & 0, multiple the number of 1's if any within E2 by 10 and the number of 1's within F2 by 5. 5) then comes the SUMPRODUCT adding up step 4. 6) Add that value to C2 and you have the answer. But I don't get the +1 and aren't really sure what the math is between steps 2&4 assuming they are generally right. Please explain further. Take your time answering as I'll be on a business trip for few days. I am appreciative of your efforts. Thanks, v!v Thanks "JMB" wrote: Perhaps: =C2+SUMPRODUCT({10,5},--(E2:F2<""),LEN(E2:F2)-LEN(SUBSTITUTE(E2:F2,",",""))+1) "v!v" wrote: Hi - Trying to turn a simple informational table into a math question here. The initial table includes the following which were initially handentered: (First row headings) col A-G NameOfPayer Check# MembershipAmount MemberName LlamaRegistered@$10ea LlamaEntered@$5ea CheckTotal (2nd row data) Thompson 2214 $30.00 Thompson Denali Denali $45.00 (3rd row data) Hester 345 blankcell blankcell blankcell Tigger,Blackie $10.00 Now the table is getting large enough, that the trial manager wants to calculate the totals after he puts in the rest of the data as a double check to what the check should have been made out for. I need to come up with that formula that generates the total as the data is entered. Some basic facts: Membership is always $30 per person, a lifetime Llama registration is $10 so they don't have to be registered every time they enter a pack trial, trial entry fees are $5 per animal every time, & multiple animals can be entered in a single trial. The total(G2) needs to be the sum of C2 plus E2 plus F2. E2 equals $10 x number of animals recorded in that cell and F2 equals $5 x number of animals recorded in that cell; animal names are separated by commas. Any ideas? Thanks -- Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please help me! making table involves percents | Excel Discussion (Misc queries) | |||
IF/Nesting When Calculation Involves a Date | Excel Worksheet Functions | |||
Functions, Arrays and number/text stored as text | Excel Worksheet Functions | |||
Index & Match functions - multiple criteria and multiple results | Excel Worksheet Functions | |||
Change text within multiple functions | Excel Worksheet Functions |