Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AHHHH-Get Data from Multiple Excel workbooks | Excel Discussion (Misc queries) | |||
view multiple files in multiple windows on multiple screens. | Excel Discussion (Misc queries) | |||
How do i auto create multiple files from 1 with multiple sheets | Excel Worksheet Functions | |||
Adding multiple worksheets | Excel Worksheet Functions | |||
Can I get the mode, min, and max with multiple criteria? | Excel Discussion (Misc queries) |