If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Messy Formula needs clean up help!
The Formula below works, but feels quite sloppy, and I'm sure it can be
accomplished in a much cleaner fashion. In addition, I attempted to add 1 more condition, but realized that I cannot add yet an 8th condition without receiving an error message (I believe the limit is 7). Any suggestions to clean the formula below up, and add the following 8th condition: =IF(A5="PD Reports Not Provided","$100.00" =IF(A5="Administrative Discharge Fee","$150.00",IF(A5="Medication Non Compliance Fee","$50.00",IF(A5="Taxi Fees","$10.00",IF(A5="Phase Program No Show Fee","$150.00",IF(A5="Record(s) Not Provided Fee","$100.00",IF(A5="BC Not Provided Fee","$50.00",IF(A5="File Re-Opening Fee","$150.00","")))))))) |
#2
|
|||
|
|||
Messy Formula needs clean up help!
Setup a lookup table and use VLOOKUP function
"Danny Boy" wrote: The Formula below works, but feels quite sloppy, and I'm sure it can be accomplished in a much cleaner fashion. In addition, I attempted to add 1 more condition, but realized that I cannot add yet an 8th condition without receiving an error message (I believe the limit is 7). Any suggestions to clean the formula below up, and add the following 8th condition: =IF(A5="PD Reports Not Provided","$100.00" =IF(A5="Administrative Discharge Fee","$150.00",IF(A5="Medication Non Compliance Fee","$50.00",IF(A5="Taxi Fees","$10.00",IF(A5="Phase Program No Show Fee","$150.00",IF(A5="Record(s) Not Provided Fee","$100.00",IF(A5="BC Not Provided Fee","$50.00",IF(A5="File Re-Opening Fee","$150.00","")))))))) |
#3
|
|||
|
|||
Messy Formula needs clean up help!
I'm somewhat of an Excel "newbie", so attempting to setup a lookup table and
use VLOOKUP function would be difficult for me. Thanks for the suggestion however. "Teethless mama" wrote: Setup a lookup table and use VLOOKUP function "Danny Boy" wrote: The Formula below works, but feels quite sloppy, and I'm sure it can be accomplished in a much cleaner fashion. In addition, I attempted to add 1 more condition, but realized that I cannot add yet an 8th condition without receiving an error message (I believe the limit is 7). Any suggestions to clean the formula below up, and add the following 8th condition: =IF(A5="PD Reports Not Provided","$100.00" =IF(A5="Administrative Discharge Fee","$150.00",IF(A5="Medication Non Compliance Fee","$50.00",IF(A5="Taxi Fees","$10.00",IF(A5="Phase Program No Show Fee","$150.00",IF(A5="Record(s) Not Provided Fee","$100.00",IF(A5="BC Not Provided Fee","$50.00",IF(A5="File Re-Opening Fee","$150.00","")))))))) |
#4
|
|||
|
|||
Messy Formula needs clean up help!
Lookup tables are a lot easier than the If statement you created. Don't be
so hard on yourself. Millions of people have used Vlookup successfully. You can too. You should also learn the difference between text and numbers. Using "$150.00" makes this text, which can't be used in other formulas. You should use 150.00, and format it for currency. Regards, Fred "Danny Boy" wrote in message ... I'm somewhat of an Excel "newbie", so attempting to setup a lookup table and use VLOOKUP function would be difficult for me. Thanks for the suggestion however. "Teethless mama" wrote: Setup a lookup table and use VLOOKUP function "Danny Boy" wrote: The Formula below works, but feels quite sloppy, and I'm sure it can be accomplished in a much cleaner fashion. In addition, I attempted to add 1 more condition, but realized that I cannot add yet an 8th condition without receiving an error message (I believe the limit is 7). Any suggestions to clean the formula below up, and add the following 8th condition: =IF(A5="PD Reports Not Provided","$100.00" =IF(A5="Administrative Discharge Fee","$150.00",IF(A5="Medication Non Compliance Fee","$50.00",IF(A5="Taxi Fees","$10.00",IF(A5="Phase Program No Show Fee","$150.00",IF(A5="Record(s) Not Provided Fee","$100.00",IF(A5="BC Not Provided Fee","$50.00",IF(A5="File Re-Opening Fee","$150.00","")))))))) |
#5
|
|||
|
|||
Messy Formula needs clean up help!
attempting to setup a lookup table and use
VLOOKUP function would be difficult for me. No it wouldn't. It's a lot easier than you think. Enter these strings in the range AA1:AA7 - Administrative Discharge Fee Medication Non Compliance Fee Taxi Fees Phase Program No Show Fee Record(s) Not Provided Fee BC Not Provided Fee File Re-Opening Fee Enter the corresponding numeric values in the range AB1:AB7 - 150 50 10 150 100 50 150 Now you have a lookup table! =VLOOKUP(A5,AA1:AB7,2,0) You don't even need to use a "lookup" function in this case: =SUMIF(AA1:AA7,A5,AB1:AB7) -- Biff Microsoft Excel MVP "Danny Boy" wrote in message ... I'm somewhat of an Excel "newbie", so attempting to setup a lookup table and use VLOOKUP function would be difficult for me. Thanks for the suggestion however. "Teethless mama" wrote: Setup a lookup table and use VLOOKUP function "Danny Boy" wrote: The Formula below works, but feels quite sloppy, and I'm sure it can be accomplished in a much cleaner fashion. In addition, I attempted to add 1 more condition, but realized that I cannot add yet an 8th condition without receiving an error message (I believe the limit is 7). Any suggestions to clean the formula below up, and add the following 8th condition: =IF(A5="PD Reports Not Provided","$100.00" =IF(A5="Administrative Discharge Fee","$150.00",IF(A5="Medication Non Compliance Fee","$50.00",IF(A5="Taxi Fees","$10.00",IF(A5="Phase Program No Show Fee","$150.00",IF(A5="Record(s) Not Provided Fee","$100.00",IF(A5="BC Not Provided Fee","$50.00",IF(A5="File Re-Opening Fee","$150.00","")))))))) |
#6
|
|||
|
|||
Messy Formula needs clean up help!
Wow, easier than I thought. Thanks Biff and everyone else.
TGIF! Dan "T. Valko" wrote: attempting to setup a lookup table and use VLOOKUP function would be difficult for me. No it wouldn't. It's a lot easier than you think. Enter these strings in the range AA1:AA7 - Administrative Discharge Fee Medication Non Compliance Fee Taxi Fees Phase Program No Show Fee Record(s) Not Provided Fee BC Not Provided Fee File Re-Opening Fee Enter the corresponding numeric values in the range AB1:AB7 - 150 50 10 150 100 50 150 Now you have a lookup table! =VLOOKUP(A5,AA1:AB7,2,0) You don't even need to use a "lookup" function in this case: =SUMIF(AA1:AA7,A5,AB1:AB7) -- Biff Microsoft Excel MVP "Danny Boy" wrote in message ... I'm somewhat of an Excel "newbie", so attempting to setup a lookup table and use VLOOKUP function would be difficult for me. Thanks for the suggestion however. "Teethless mama" wrote: Setup a lookup table and use VLOOKUP function "Danny Boy" wrote: The Formula below works, but feels quite sloppy, and I'm sure it can be accomplished in a much cleaner fashion. In addition, I attempted to add 1 more condition, but realized that I cannot add yet an 8th condition without receiving an error message (I believe the limit is 7). Any suggestions to clean the formula below up, and add the following 8th condition: =IF(A5="PD Reports Not Provided","$100.00" =IF(A5="Administrative Discharge Fee","$150.00",IF(A5="Medication Non Compliance Fee","$50.00",IF(A5="Taxi Fees","$10.00",IF(A5="Phase Program No Show Fee","$150.00",IF(A5="Record(s) Not Provided Fee","$100.00",IF(A5="BC Not Provided Fee","$50.00",IF(A5="File Re-Opening Fee","$150.00","")))))))) . |
#7
|
|||
|
|||
Messy Formula needs clean up help! (Thought I had it, but I don't)
Hi Biff!
I tried your suggestion, and I'm still a bit confused (sorry)! My first question is about your formula. =VLOOKUP(A5,AA1:AB7,2,0) 1-What does cell A5 reference? Given that the strings and corresponding numbers are in AA1-AA7 and AB1-AB7. 2-In what cell do I put the VLOOKUP formula so that it runs? What I'm trying to (as you may have guessed), is to use a drop down menu with the various charge types (Administrative Discharge Fee, Medication Non Compliance Fee, Taxi Fees, etc) in one column, and then have the charges themselves appear in the corresponding columns. If the drop down menu is blank, than no charges would appear. Again, Thank You "T. Valko" wrote: attempting to setup a lookup table and use VLOOKUP function would be difficult for me. No it wouldn't. It's a lot easier than you think. Enter these strings in the range AA1:AA7 - Phase Program No Show Fee Record(s) Not Provided Fee BC Not Provided Fee File Re-Opening Fee Enter the corresponding numeric values in the range AB1:AB7 - 150 50 10 150 100 50 150 Now you have a lookup table! =VLOOKUP(A5,AA1:AB7,2,0) You don't even need to use a "lookup" function in this case: =SUMIF(AA1:AA7,A5,AB1:AB7) -- Biff Microsoft Excel MVP "Danny Boy" wrote in message ... I'm somewhat of an Excel "newbie", so attempting to setup a lookup table and use VLOOKUP function would be difficult for me. Thanks for the suggestion however. "Teethless mama" wrote: Setup a lookup table and use VLOOKUP function "Danny Boy" wrote: The Formula below works, but feels quite sloppy, and I'm sure it can be accomplished in a much cleaner fashion. In addition, I attempted to add 1 more condition, but realized that I cannot add yet an 8th condition without receiving an error message (I believe the limit is 7). Any suggestions to clean the formula below up, and add the following 8th condition: =IF(A5="PD Reports Not Provided","$100.00" =IF(A5="Administrative Discharge Fee","$150.00",IF(A5="Medication Non Compliance Fee","$50.00",IF(A5="Taxi Fees","$10.00",IF(A5="Phase Program No Show Fee","$150.00",IF(A5="Record(s) Not Provided Fee","$100.00",IF(A5="BC Not Provided Fee","$50.00",IF(A5="File Re-Opening Fee","$150.00","")))))))) . |
#8
|
|||
|
|||
Messy Formula needs clean up help! (Thought I had it, but I don't)
VLOOKUP
=VLOOKUP(lookup item,lookup table,column index number,true or false) A5 would be the cell with the chosen item Gord Dibben MS Excel MVP On Fri, 19 Mar 2010 05:38:05 -0700, Danny Boy wrote: Hi Biff! I tried your suggestion, and I'm still a bit confused (sorry)! My first question is about your formula. =VLOOKUP(A5,AA1:AB7,2,0) 1-What does cell A5 reference? Given that the strings and corresponding numbers are in AA1-AA7 and AB1-AB7. 2-In what cell do I put the VLOOKUP formula so that it runs? What I'm trying to (as you may have guessed), is to use a drop down menu with the various charge types (Administrative Discharge Fee, Medication Non Compliance Fee, Taxi Fees, etc) in one column, and then have the charges themselves appear in the corresponding columns. If the drop down menu is blank, than no charges would appear. Again, Thank You "T. Valko" wrote: attempting to setup a lookup table and use VLOOKUP function would be difficult for me. No it wouldn't. It's a lot easier than you think. Enter these strings in the range AA1:AA7 - Phase Program No Show Fee Record(s) Not Provided Fee BC Not Provided Fee File Re-Opening Fee Enter the corresponding numeric values in the range AB1:AB7 - 150 50 10 150 100 50 150 Now you have a lookup table! =VLOOKUP(A5,AA1:AB7,2,0) You don't even need to use a "lookup" function in this case: =SUMIF(AA1:AA7,A5,AB1:AB7) -- Biff Microsoft Excel MVP "Danny Boy" wrote in message ... I'm somewhat of an Excel "newbie", so attempting to setup a lookup table and use VLOOKUP function would be difficult for me. Thanks for the suggestion however. "Teethless mama" wrote: Setup a lookup table and use VLOOKUP function "Danny Boy" wrote: The Formula below works, but feels quite sloppy, and I'm sure it can be accomplished in a much cleaner fashion. In addition, I attempted to add 1 more condition, but realized that I cannot add yet an 8th condition without receiving an error message (I believe the limit is 7). Any suggestions to clean the formula below up, and add the following 8th condition: =IF(A5="PD Reports Not Provided","$100.00" =IF(A5="Administrative Discharge Fee","$150.00",IF(A5="Medication Non Compliance Fee","$50.00",IF(A5="Taxi Fees","$10.00",IF(A5="Phase Program No Show Fee","$150.00",IF(A5="Record(s) Not Provided Fee","$100.00",IF(A5="BC Not Provided Fee","$50.00",IF(A5="File Re-Opening Fee","$150.00","")))))))) . |
Thread Tools | |
Display Modes | |
|
|