Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
What formula/function can I use?
I'm creating a spreadsheet for a client and I'm not sure what
formula/function I need to do this: I created a table with over 500 available values. I then created (on a separate sheet) a form to input the customers information. 4 pieces of the customer's information need to be used to figure out the correct price - Age, Tobacco or No, Male or Female, and amount of coverage. I need to be able to reference the right cell in my table according to how the 4 above criteria are met, and plug that value back on to my customer information page to show the customer their cost for choosing that amount of coverage. Hope that makes sense and any help will be appreciated :-) -- Thanks, mekus31 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
What formula/function can I use?
For multi-criteria retrievals, you could use an array-entered* INDEX/MATCH,
something like this: =INDEX(UnitCost,MATCH(1,(Age=G2)*(Smoker=H2)*(Sex= I2)*(CoverAmt=J2),0)) where UnitCost, Age, Smoker, Sex, CoverAmt are all identically sized defined col ranges in your reference table, and G2:J2 contains the respective 4 variable inputs. UnitCost is what you want retrieved (the INDEX(UnitCost, .. part) given the inputs for the rest of the 4 variables. *Array-enter to press CTRL+SHIFT+ENTER to confirm the formula, instead of just pressing ENTER. Correctly done, Excel will wrap curly braces { } around the formula -- see this happening (within the formula bar) as a visual check that it's correctly array-entered. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mekus31" wrote: I'm creating a spreadsheet for a client and I'm not sure what formula/function I need to do this: I created a table with over 500 available values. I then created (on a separate sheet) a form to input the customers information. 4 pieces of the customer's information need to be used to figure out the correct price - Age, Tobacco or No, Male or Female, and amount of coverage. I need to be able to reference the right cell in my table according to how the 4 above criteria are met, and plug that value back on to my customer information page to show the customer their cost for choosing that amount of coverage. Hope that makes sense and any help will be appreciated :-) -- Thanks, mekus31 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
What formula/function can I use?
I appreciate the response Max.
I tried it but unfortunately I think that stuff is way over my head. I've figured out a different way to do it (all 1680 possibilities, ugh) by using the IF command but now I'm having a problem with the value showing false when I take out the coverage amount (in case they don't want to buy that endorsement). Is there a way to tell it not to show "False," only a value if criteria is met? BTW where do you learn all the advanced stuff? I have all the usual Step by Step and Excel for Dummies books, but they're pretty much beginners books. Thanks again -- Thanks, mekus31 "Max" wrote: For multi-criteria retrievals, you could use an array-entered* INDEX/MATCH, something like this: =INDEX(UnitCost,MATCH(1,(Age=G2)*(Smoker=H2)*(Sex= I2)*(CoverAmt=J2),0)) where UnitCost, Age, Smoker, Sex, CoverAmt are all identically sized defined col ranges in your reference table, and G2:J2 contains the respective 4 variable inputs. UnitCost is what you want retrieved (the INDEX(UnitCost, .. part) given the inputs for the rest of the 4 variables. *Array-enter to press CTRL+SHIFT+ENTER to confirm the formula, instead of just pressing ENTER. Correctly done, Excel will wrap curly braces { } around the formula -- see this happening (within the formula bar) as a visual check that it's correctly array-entered. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mekus31" wrote: I'm creating a spreadsheet for a client and I'm not sure what formula/function I need to do this: I created a table with over 500 available values. I then created (on a separate sheet) a form to input the customers information. 4 pieces of the customer's information need to be used to figure out the correct price - Age, Tobacco or No, Male or Female, and amount of coverage. I need to be able to reference the right cell in my table according to how the 4 above criteria are met, and plug that value back on to my customer information page to show the customer their cost for choosing that amount of coverage. Hope that makes sense and any help will be appreciated :-) -- Thanks, mekus31 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
What formula/function can I use?
.. Is there a way to tell it not to show "False," only a value if
criteria is met? Perhaps you could post your current IF formula .. Being specific would certainly help attract responses BTW where do you learn all the advanced stuff? well, you could start learning right-here in these excel newsgroups by reading & trying out the responses to the many posts. takes effort, but there's great variety around both in queries asked and responses given. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mekus31" wrote: I appreciate the response Max. I tried it but unfortunately I think that stuff is way over my head. I've figured out a different way to do it (all 1680 possibilities, ugh) by using the IF command but now I'm having a problem with the value showing false when I take out the coverage amount (in case they don't want to buy that endorsement). Is there a way to tell it not to show "False," only a value if criteria is met? BTW where do you learn all the advanced stuff? I have all the usual Step by Step and Excel for Dummies books, but they're pretty much beginners books. Thanks again -- Thanks, mekus31 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
What formula/function can I use?
I tried it but unfortunately I think that stuff is way over my head ..
Perhaps try this sample from my archives* which illustrates a multi-criteria INDEX/MATCH in action: http://savefile.com/files/555218 Matching multiple criteria.xls *Link is in my sign-off. 100's of samples available for easy reference. All FOC, all are welcome. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
What formula/function can I use?
Ok Max, what I thought would work isn't working anyway. This is what I'm
trying to do: Sheet 1 questions: Gender: M or F Tobacco: Y or N Age: (options are) 18-59 Coverage desired: (options are) 5,000 - 50,000 increments of 5 Sheet 2: A table with 1680 possible combinations depending on how they answer gender, tobacco, age, & coverage amt What I'm trying to do, for example, if the above answers are Male(M), No Tobacco(N), age 25(25), and $5,000 (coverage amt) I want the cell with the price to answer $6.80 which will be the monthly charge they have to pay. You see, what I'm trying to get it to do is look at the values in 4 cells, to determine what the value should be for the final cell(using my table on the second sheet). I wish I could input my spreadsheet here, it would be way easier to explain. Hopefully I explained it ok. I downloaded the example you referred me to and am trying to figure it out. If only my client would use Access, this would be much easier. -- Thanks, mekus31 "Max" wrote: .. Is there a way to tell it not to show "False," only a value if criteria is met? Perhaps you could post your current IF formula .. Being specific would certainly help attract responses BTW where do you learn all the advanced stuff? well, you could start learning right-here in these excel newsgroups by reading & trying out the responses to the many posts. takes effort, but there's great variety around both in queries asked and responses given. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mekus31" wrote: I appreciate the response Max. I tried it but unfortunately I think that stuff is way over my head. I've figured out a different way to do it (all 1680 possibilities, ugh) by using the IF command but now I'm having a problem with the value showing false when I take out the coverage amount (in case they don't want to buy that endorsement). Is there a way to tell it not to show "False," only a value if criteria is met? BTW where do you learn all the advanced stuff? I have all the usual Step by Step and Excel for Dummies books, but they're pretty much beginners books. Thanks again -- Thanks, mekus31 |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
What formula/function can I use?
Perhaps you could use either of the 2 free filehosts listed below to upload
your sample file and then post the link to it in response here (the link is generated when you upload, just copy and paste it here): http://www.flypicture.com/ http://cjoint.com/index.php For cjoint.com (it's in French), just click the "Browse" button, navigate to folder select the file Open, then click the button centred in the page below labelled "Creer le lien Cjoint") and it'll generate the link. Then copy & paste the generated link as part and parcel of your response here. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mekus31" wrote: Ok Max, what I thought would work isn't working anyway. This is what I'm trying to do: Sheet 1 questions: Gender: M or F Tobacco: Y or N Age: (options are) 18-59 Coverage desired: (options are) 5,000 - 50,000 increments of 5 Sheet 2: A table with 1680 possible combinations depending on how they answer gender, tobacco, age, & coverage amt What I'm trying to do, for example, if the above answers are Male(M), No Tobacco(N), age 25(25), and $5,000 (coverage amt) I want the cell with the price to answer $6.80 which will be the monthly charge they have to pay. You see, what I'm trying to get it to do is look at the values in 4 cells, to determine what the value should be for the final cell(using my table on the second sheet). I wish I could input my spreadsheet here, it would be way easier to explain. Hopefully I explained it ok. I downloaded the example you referred me to and am trying to figure it out. If only my client would use Access, this would be much easier. |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
What formula/function can I use?
Ok, I've uploaded my spreadsheet to flypic. Here's the link:
http://www.flypicture.com/files/MjM4NjQ5 The explanation of what I'm trying to do is on the spreadsheet itself. -- Thanks, mekus31 "Max" wrote: Perhaps you could use either of the 2 free filehosts listed below to upload your sample file and then post the link to it in response here (the link is generated when you upload, just copy and paste it here): http://www.flypicture.com/ http://cjoint.com/index.php For cjoint.com (it's in French), just click the "Browse" button, navigate to folder select the file Open, then click the button centred in the page below labelled "Creer le lien Cjoint") and it'll generate the link. Then copy & paste the generated link as part and parcel of your response here. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mekus31" wrote: Ok Max, what I thought would work isn't working anyway. This is what I'm trying to do: Sheet 1 questions: Gender: M or F Tobacco: Y or N Age: (options are) 18-59 Coverage desired: (options are) 5,000 - 50,000 increments of 5 Sheet 2: A table with 1680 possible combinations depending on how they answer gender, tobacco, age, & coverage amt What I'm trying to do, for example, if the above answers are Male(M), No Tobacco(N), age 25(25), and $5,000 (coverage amt) I want the cell with the price to answer $6.80 which will be the monthly charge they have to pay. You see, what I'm trying to get it to do is look at the values in 4 cells, to determine what the value should be for the final cell(using my table on the second sheet). I wish I could input my spreadsheet here, it would be way easier to explain. Hopefully I explained it ok. I downloaded the example you referred me to and am trying to figure it out. If only my client would use Access, this would be much easier. |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
What formula/function can I use?
Hi Mekus,
I haven't got time to follow through with this at the moment but you could simplify things by splitting your critical sheet into another 4 sheets, called MY, MN, FY and FN. Then in your lookup formula to address the right sheet use =B7&F5&"!" That takes 2 of your criteria out of the lookup for you. HTH Martin "mekus31" wrote in message ... Ok, I've uploaded my spreadsheet to flypic. Here's the link: http://www.flypicture.com/files/MjM4NjQ5 The explanation of what I'm trying to do is on the spreadsheet itself. -- Thanks, mekus31 "Max" wrote: Perhaps you could use either of the 2 free filehosts listed below to upload your sample file and then post the link to it in response here (the link is generated when you upload, just copy and paste it here): http://www.flypicture.com/ http://cjoint.com/index.php For cjoint.com (it's in French), just click the "Browse" button, navigate to folder select the file Open, then click the button centred in the page below labelled "Creer le lien Cjoint") and it'll generate the link. Then copy & paste the generated link as part and parcel of your response here. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mekus31" wrote: Ok Max, what I thought would work isn't working anyway. This is what I'm trying to do: Sheet 1 questions: Gender: M or F Tobacco: Y or N Age: (options are) 18-59 Coverage desired: (options are) 5,000 - 50,000 increments of 5 Sheet 2: A table with 1680 possible combinations depending on how they answer gender, tobacco, age, & coverage amt What I'm trying to do, for example, if the above answers are Male(M), No Tobacco(N), age 25(25), and $5,000 (coverage amt) I want the cell with the price to answer $6.80 which will be the monthly charge they have to pay. You see, what I'm trying to get it to do is look at the values in 4 cells, to determine what the value should be for the final cell(using my table on the second sheet). I wish I could input my spreadsheet here, it would be way easier to explain. Hopefully I explained it ok. I downloaded the example you referred me to and am trying to figure it out. If only my client would use Access, this would be much easier. |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
What formula/function can I use?
One way to deliver it here ..
In Client Info Sheet, Place in K16, normal ENTER will do: =IF(COUNTA(B5,B7,F5,F16)<4,"",INDEX(OFFSET('Critic al Care Endors'!$A:$A,,MATCH(IF(B7="M",B7&"ale "&F5&" Tobacco",IF(B7="F",B7&"emale "&F5&" Tobacco","")),'Critical Care Endors'!$1:$1,0)-1+F16/5000,),MATCH(B5,'Critical Care Endors'!$A:$A,0))) Here's a link to an implemented sample: http://www.flypicture.com/download/MjM4ODM5 MultiCriteria Extract fr Actuarial Table.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mekus31" wrote: Ok, I've uploaded my spreadsheet to flypic. Here's the link: http://www.flypicture.com/files/MjM4NjQ5 The explanation of what I'm trying to do is on the spreadsheet itself. -- Thanks, mekus31 |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
What formula/function can I use?
Thanks Max, appreciate the formula.
I'm now in the process of figuring out what all the things are that you used. Match, Offset, etc. so I may come back at you w/ a couple questions if that's ok. It's important for me to figure out what you did so I can repeat it with other options. Anyway, thanks again :-) -- Thanks, mekus31 "Max" wrote: One way to deliver it here .. In Client Info Sheet, Place in K16, normal ENTER will do: =IF(COUNTA(B5,B7,F5,F16)<4,"",INDEX(OFFSET('Critic al Care Endors'!$A:$A,,MATCH(IF(B7="M",B7&"ale "&F5&" Tobacco",IF(B7="F",B7&"emale "&F5&" Tobacco","")),'Critical Care Endors'!$1:$1,0)-1+F16/5000,),MATCH(B5,'Critical Care Endors'!$A:$A,0))) Here's a link to an implemented sample: http://www.flypicture.com/download/MjM4ODM5 MultiCriteria Extract fr Actuarial Table.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mekus31" wrote: Ok, I've uploaded my spreadsheet to flypic. Here's the link: http://www.flypicture.com/files/MjM4NjQ5 The explanation of what I'm trying to do is on the spreadsheet itself. -- Thanks, mekus31 |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
What formula/function can I use?
"mekus31" wrote:
Thanks Max, appreciate the formula. welcome. glad that got you going here. I'm now in the process of figuring out what all the things are that you used. Match, Offset, etc. so I may come back at you w/ a couple questions if that's ok. It's important for me to figure out what you did so I can repeat it with other options. Usually it's best to put this kind of request in as a new posting. But I'll try explain it here for you .. The core challenging part here is to be able grab the correct column from the reference table "as-is" (ie the table in sheet: Critical Care Endors) and then use this in a INDEX(Correct Col,Correct Row) construct to return the required cost value within the Correct Col. The Correct Col is returned via using: OFFSET(RefRange,,Col param,) A study of the ref table reveals fortunately that we can use the col labels in the first row, ie that there is regularity, a sort of pattern which would simplify things. The key pattern here would be that the "Male N Tobacco", "Male Y Tobacco", "Female N Tobacco" "divider" cols are consistently named in themselves, and importantly, the "Face Amt" col labels which are adjacent to each of the divider cols are also consistently structured in name, number and placement. There's 10 of these "Face Amt" col labels to the right of each divider col, eg: 5,000, 10,000, ... 50,000. OFFSET(RefRange,,Col param,) where RefRange = 'Critical Care Endors'!$A:$A will return a corresponding col range defined by the Col Param If Col param is say = 1, OFFSET('Critical Care Endors'!$A:$A,,2,) returns the col range 'Critical Care Endors'!$B:$B, ie 1 col to the right of the RefRange. Col param = MATCH(concat string,1st row of reference table,0)+ an arithmetic adjustment where concat string = IF(B7="M",B7&"ale "&F5&" Tobacco",IF(B7="F",B7&"emale "&F5&" Tobacco","")) The concat string IF formula essentially composes the various DV inputs in "Client Info Sheet" for an exact match with one of the divider cols, eg:"Male Y Tobacco", "Female N Tobacco" etc within the 1st row of the reference table, ie: 'Critical Care Endors'!$1:$1. The number returned by the MATCH() is the position of the label. The arithmetic adjustment part: -1+F16/5000 applied then adjusts this label position number returned by the MATCH() to yield the final, correct number for the Col param depending on the Face Amt DV input (the input in F16). With the Correct Col grabbed/returned by the OFFSET(RefRange,,Col param,), INDEX(Correct Col,Correct Row) where Correct Row = MATCH(B5,'Critical Care Endors'!$A:$A,0)) then simply matches the Age DV input in B5 with the Issue Age col: 'Critical Care Endors'!$A:$A to return the correct row, and hence the required intersection point value (the Cost) Finally, the front IF part of it: =IF(COUNTA(B5,B7,F5,F16)<4,"", ...) simply ensures that all 4 the DV inputs in B5,B7,F5,F16 are selected before proceeding to calc INDEX(Correct Col,Correct Row). If any one DV is still empty (ie cleared with the Delete key, not selected yet by user) then you have a neat blank:"" appearing in the formula cell. Hope the above helps to explain it ok for your easy cross application elsewhere. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
What formula/function can I use?
Typo in line:
If Col param is say = 1, OFFSET('Critical Care Endors'!$A:$A,,2,) returns should have read as: If Col param is say = 1, OFFSET('Critical Care Endors'!$A:$A,,1,) returns -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i add a function to a formula? | Excel Discussion (Misc queries) | |||
A formula/function to return a formula/function | Excel Worksheet Functions | |||
Formula...function...how to? | Excel Worksheet Functions | |||
Need Help With Formula/Function | Excel Worksheet Functions | |||
Help with Formula Function | Excel Worksheet Functions |