Nested Functions with OR

[bill ch]

I would like to add multiple IF / VLOOKUPS to the formula below that
currently works. This site: http://www.cpearson.com/excel/nested.htm helps
but, I was planning on using the named formula to lookup '2006 IP Payer Mix
on Settled' vs '2005 IP Payer Mix on Settled'. Currently I have multiple
columns that search based on the IF(B31="*",) with "*" equalling various
letters. IS there a way I can combine these with an OR statement or am I
heading down the wrong path. Suggestions welcome, thanks. Please keep in
mind the file is large 28MB.


=IF(AC31=J31,J31*0.5,IF(D31=" ",VLOOKUP(C31,'2006 IP Payer Mix on
Settled'!H:I,2,FALSE)*K31,IF(B31="A",VLOOKUP(D31,'2006 IP
Template'!B:C,2,FALSE),IF(B31="B",VLOOKUP(D31,'2006 IP
Template'!B:G,6,FALSE),IF(B31="h",VLOOKUP(C31,'2006 IP Payer Mix on
Settled'!H:I,2,FALSE)*K31,IF(B31="d",VLOOKUP(D31,'2006 IP
Template'!B:Y,10,FALSE),IF(C31="D05",VLOOKUP(D31,'2006 IP
Template'!B:Y,11,FALSE))))))))

 

[pdberger]

Bill --

I think you ought to look at the CHOOSE function. The first argument is an
index number ('n'), and then there are 'n' different actions. CHOOSE picks
the nth action. So then the problem is that you've already set it up with
letters rather than index numbers (1, 2, 3, 4, 5). I 'd think you have two
choices, either find-and-replace all the letters with numbers, or create a
little lookup table where it looks up the index number based on the letter
you assigned.

HTH

 

[bill ch]

pdberger,
The letter represents the first digit out of a three digit health insurance
payor code. So, I set-up the lookup table to assign an index number. The
CHOOSE is excellent and I have it set to choose a named formula based on the
index #. My only problem now is I have to paste the choose formula in a
column down multiple rows. How do I change the original formulas so, the row
that it is looking at '31' will correspond to the row that the choose formula
is in.
Hope this makes sense -Thanks

 

[pdberger]

Bill --
If I understand your question, you want to copy this CHOOSE formula down a
bunch of rows, and have it use the information from that row when
appropriate, but pull information from the lookup table you created (which
doesn't move).
To do that, you'll need to understand absolute and relative references.
Read about it in Excel help. Basically, as you copy a formla, all the cell
references track along RELATIVE to the movement. So if your initial cell
makes reference to the cell one column to the left, wherever you copy the
formula will look to the cell one column to the left of THAT. The bigger
problem is to make the references not track along, remaining ABSOLUTEly fixed
on one location. To do that, put a dollar sign ($) in front of the letter
and number. So the reference A1 in a formula would track along as you copied
the cell elsewhere, while the reference $A$1 would always remain fixed,
aiming at $A$1 forever.

Is that what you wanted, I hope/

 

[bill ch]

pdberger,
Sorry, I should've used an example to explain this. The index numbers are
in column B, it is set-up as a vlookup to the index table I created.
Example: now I have a CHOOSE formula of
'=IF(AC31=J31,J31*0.5,CHOOSE(B31,("A","B","H","D")))
When I paste this down the rows it will do a relative reference for AC31,
J31 and B31 by changing them to AC32, J32, and B32 and so on. My problem is
it won't make the named formula references"A","B","H","D") relative
references. The Named Formula's are:
"A" =VLOOKUP(D31,'2006 IP Template'!B:C,2,FALSE)
"B" =VLOOKUP(D31,'2006 IP Template'!B:G,6,FALSE)
"H" =VLOOKUP(C31,'2006 IP Payer Mix on Settled'!H:I,2,FALSE)*K31
"D" =VLOOKUP(D31,'2006 IP Template'!B:Y,10,FALSE)
The CHOOSE formula will eventually use almost all 29 index values so, it
will be too big for me to paste all the formulas into the CHOOSE formula
=exceeds the cell limit. Thus, I've set them up as named formulas for the
CHOOSE formula to look-up. The ultimate goal is: How can I get these named
formulas to also be relative references?? When in row 32 I need these to
look at D32, C32 and K32.
Thanks

 

[pdberger]

Bill --
It's getting too complicated for me to follow w/o actually seeing it, so I'm
going to have to bow out. I'd offer trying to move the CHOOSE function
outside of the named formula -- that's what I anticipated when I suggested
the function.

Good luck