IF function - need more than 7 nests...(help?)

  • Thread starter Thread starter Jmo
  • Start date Start date
J

Jmo

I have a workbook where one sheet is pulling membership
dues based on the census of the group. If the membership
is 41, it goes to the Dues sheet and finds the cell with
the <50 members fee. The formula is working with the 7
nests, but that is all that is allowed. I need 11.

Is there any way to work around this or should I be using
something else?

Thanks!
 
Jmo,

You need a lookup table with a column for the census value, 0, 50, etc., and
a column for the dues. Then you use a formula like

=VLOOKUP(thisvalue,Dues!A1:B20,2)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Post your formula, most likely you can use a lookup table
to solve this. put the membership values breaking points
in ascending order

0 0
20 10
50 20
70 30
90 40

then use

=VLOOKUP(A1,'Dues'!A2:B10,2)

so for instance 41 would find 20 and return 10 from the adjacent cell

adapt to fit your data
 
Amen. And the VLOOKUP, once understood, is neater and cleaner than a mess
of embedded IFs (especially if split into more than one cell to get more
than 7). Less chance of an error. It'll be easier to change the breaks in
your value ranges, should that be necessary, or to add or remove one. You
just modify the table.

Don't forget to make the reference to the table absolute, if you'll be
copying the VLOOKUP into other cells.

=VLOOKUP(A1,'Dues'!$A$2:$B$10,2)

Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------
 
Back
Top