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 |
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) |