Alternative to multiple IF limits
 

Dear All,

I have the following problem:

I have a spreadsheet with two worksheets - first one is the master input
sheet, the second is performing all the calculations behind the scenes. On
the second one I have a table setup which works for cross reference - it has
30 columns C2:AG containing headers incrementing by 100 which are width
values ie. 1000,1100,1200,1300,1400..4000. The table has 30 rows B3:B32 with
row headers again incrementing by 100 which are height values - 1000,1100
etc. Each colum and row value has a crossreferenced value within the table
ie. 1300 (F2) and 1900(B12) meet at F12 with a value of 600.

Currently we have been entering a value into the master sheet for example
1252 for width and height 1453 - I have then been setting a value of a two
cells using:
Cell 1:
=IF(cs!B2+cs!M2<1001,"C",IF(cs!B2+cs!M2<1101,"D",I F(cs!B2+cs!M2<1201,"E",IF(cs!B2+cs!M2<1301,"F",IF( cs!B2+cs!M2<1401,"G",IF(cs!B2+cs!M2<1501,"H",IF(cs !B2+cs!M2<1601,"I",IF(cs!B2+cs!M2<1701,"J","!")))) ))))
Cell 2:
=IF(cs!C2+cs!N2<1001,"3",IF(cs!C2+cs!N2<1101,"4",I F(cs!C2+cs!N2<1201,"5",IF(cs!C2+cs!N2<1301,"6",IF( cs!C2+cs!N2<1401,"7",IF(cs!C2+cs!N2<1501,"8",IF(cs !C2+cs!N2<1601,"9",IF(cs!C2+cs!N2<1701,"10","!"))) )))))

This would then produce in cell 1:F cell 2:8 which I have then been using
=CONCATENATE(B52,C52) to populate F8 into that cell and then using
=INDIRECT(D52) for the value of the cell that the concatenate relates to.
Although probably a long winded way of working out the value it works - I'm
open to alternative suggestions - the main problem I have is that I obviously
need 30 IF statements to get this to work like this - which I know Excel
won't let me - so anyone point me in the direction of a solution that works.

Thanks in advance

Matt

Alternative to multiple IF limits
 

You can easily use the MATCH() function to figure out which column and which
row to use. Those two values would be arguments to the INDEX() function

Alternative to multiple IF limits
 

Hi Duke,

Thanks for replying - I tried using the match() function before but if I was
entering 1252 for the value - I found it found the location for 1200 rather
than 1300 as we have to round up the value, and by adding 1 to the location
it caused problems if it was the exact number being searched ie. 1300 would
then return the position for 1400 - I may be doing something wrong.

"Duke Carey" wrote:

You can easily use the MATCH() function to figure out which column and which
row to use. Those two values would be arguments to the INDEX() function

Alternative to multiple IF limits
 

You want to use

=MATCH( value, range, -1)


From the Help file on Match(), regarding the 3rd argument:

Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft
Excel matches lookup_value with values in lookup_array.

If match_type is 1, MATCH finds the largest value that is less than or equal
to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1,
0, 1, 2, ..., A-Z, FALSE, TRUE.

If match_type is 0, MATCH finds the first value that is exactly equal to
lookup_value. Lookup_array can be in any order.

If match_type is -1, MATCH finds the smallest value that is greater than or
equal to lookup_value. Lookup_array must be placed in descending order: TRUE,
FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

Alternative to multiple IF limits
 

Doesn't work as the values of the cell ascend - I've even tried changing the
formula to descend ie. C32:C3 - but it automatically swops back round

"Duke Carey" wrote:

You want to use

=MATCH( value, range, -1)


From the Help file on Match(), regarding the 3rd argument:

Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft
Excel matches lookup_value with values in lookup_array.

If match_type is 1, MATCH finds the largest value that is less than or equal
to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1,
0, 1, 2, ..., A-Z, FALSE, TRUE.

If match_type is 0, MATCH finds the first value that is exactly equal to
lookup_value. Lookup_array can be in any order.

If match_type is -1, MATCH finds the smallest value that is greater than or
equal to lookup_value. Lookup_array must be placed in descending order: TRUE,
FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

Alternative to multiple IF limits
 

You'd have to re-order the columns from high to low; same iwth the rows.

If you don't want to do that, use

=MATCH(ROUNDUP(value,-2),array,1)

=ROUNDUP(1225,-2) returns 1300
=ROUNDUP(1200,-2) returns 1200


"Matt Simms" wrote:

Doesn't work as the values of the cell ascend - I've even tried changing the
formula to descend ie. C32:C3 - but it automatically swops back round

"Duke Carey" wrote:

You want to use

=MATCH( value, range, -1)


From the Help file on Match(), regarding the 3rd argument:

Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft
Excel matches lookup_value with values in lookup_array.

If match_type is 1, MATCH finds the largest value that is less than or equal
to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1,
0, 1, 2, ..., A-Z, FALSE, TRUE.

If match_type is 0, MATCH finds the first value that is exactly equal to
lookup_value. Lookup_array can be in any order.

If match_type is -1, MATCH finds the smallest value that is greater than or
equal to lookup_value. Lookup_array must be placed in descending order: TRUE,
FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.