Messy Formula needs clean up help!

  • Thread starter Thread starter Danny Boy
  • Start date Start date
D

Danny Boy

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",""))))))))
 
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.
 
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
 
attempting to setup a lookup table and use
VLOOKUP function would be difficult for me.
Click to expand...

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)
 
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
 
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
 
Back
Top