A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Messy Formula needs clean up help!



 
 
Thread Tools Display Modes
  #1  
Old March 19th, 2010, 01:51 AM posted to microsoft.public.excel.worksheet.functions
Danny boy
external usenet poster
 
Posts: 106
Default 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  
Old March 19th, 2010, 02:33 AM posted to microsoft.public.excel.worksheet.functions
Teethless mama
external usenet poster
 
Posts: 3,722
Default 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  
Old March 19th, 2010, 02:54 AM posted to microsoft.public.excel.worksheet.functions
Danny boy
external usenet poster
 
Posts: 106
Default 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  
Old March 19th, 2010, 03:34 AM posted to microsoft.public.excel.worksheet.functions
Fred Smith[_4_]
external usenet poster
 
Posts: 2,386
Default 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  
Old March 19th, 2010, 03:42 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default 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  
Old March 19th, 2010, 12:05 PM posted to microsoft.public.excel.worksheet.functions
Danny boy
external usenet poster
 
Posts: 106
Default 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  
Old March 19th, 2010, 01:38 PM posted to microsoft.public.excel.worksheet.functions
Danny boy
external usenet poster
 
Posts: 106
Default 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  
Old March 19th, 2010, 04:24 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:03 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.