If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need help with Excel formula
How do I create a formula that will return a number 1-10 depending on data
entered ranging from 1-57. Example. I have 10 fire districts. Each district has about 5 Fire Stations in no particular order. If I put in fire station number 10 then the fire district 2 will auto fill-in. Here is my formula that i am trying to build but it's limited to a certain number I think 8. =IF(H26=3,1,IF(H26=4,1,IF(H26=6,1,IF(H26=18,1,IF(H 26=24,1,IF(H26=7,2,IF(H26=10,2,IF(H26=20,2,0)))))) )) Can I group numbers together in the formula such as Fire Stations 3, 4, 6, 18, or 24 = 1 7, 10, 13, 20 or 56 =2 8, 11, 17, 19 or 44 = 3 ...... = 4 ...... = 5 ...... = 6 ...... = 7 ...... = 8 ...... = 9 ...... = 10 Thanks very much! -- Firefighter Instructor |
#2
|
|||
|
|||
Need help with Excel formula
Create a 2 column table like this...
A1:A57 = station number B1:B57 = district number It would look something like this... Station1...5 Station2...3 Station3...1 Station4...7 Station5...9 etc etc Then the formula would be... =VLOOKUP(H26,A1:B57,2,0) -- Biff Microsoft Excel MVP "Firefighter Instructor" wrote in message ... How do I create a formula that will return a number 1-10 depending on data entered ranging from 1-57. Example. I have 10 fire districts. Each district has about 5 Fire Stations in no particular order. If I put in fire station number 10 then the fire district 2 will auto fill-in. Here is my formula that i am trying to build but it's limited to a certain number I think 8. =IF(H26=3,1,IF(H26=4,1,IF(H26=6,1,IF(H26=18,1,IF(H 26=24,1,IF(H26=7,2,IF(H26=10,2,IF(H26=20,2,0)))))) )) Can I group numbers together in the formula such as Fire Stations 3, 4, 6, 18, or 24 = 1 7, 10, 13, 20 or 56 =2 8, 11, 17, 19 or 44 = 3 ..... = 4 ..... = 5 ..... = 6 ..... = 7 ..... = 8 ..... = 9 ..... = 10 Thanks very much! -- Firefighter Instructor |
#3
|
|||
|
|||
Need help with Excel formula
if(or(H26=3,H26=4,H26=6,H26=18,H26=24),1,if(or(H26 =7,H26=10,H26=13,H26=20,H26=56),2................. .......
Another method might be to set up a table and do a VLOOKUP against the table. Irie! "Firefighter Instructor" wrote: How do I create a formula that will return a number 1-10 depending on data entered ranging from 1-57. Example. I have 10 fire districts. Each district has about 5 Fire Stations in no particular order. If I put in fire station number 10 then the fire district 2 will auto fill-in. Here is my formula that i am trying to build but it's limited to a certain number I think 8. =IF(H26=3,1,IF(H26=4,1,IF(H26=6,1,IF(H26=18,1,IF(H 26=24,1,IF(H26=7,2,IF(H26=10,2,IF(H26=20,2,0)))))) )) Can I group numbers together in the formula such as Fire Stations 3, 4, 6, 18, or 24 = 1 7, 10, 13, 20 or 56 =2 8, 11, 17, 19 or 44 = 3 ..... = 4 ..... = 5 ..... = 6 ..... = 7 ..... = 8 ..... = 9 ..... = 10 Thanks very much! -- Firefighter Instructor |
#4
|
|||
|
|||
Need help with Excel formula
Thanks very much!
I was able to group them together, but had a limit of seven and I needed 10. The other method "VLOOKUP" was the way to go. I had around 1700 data entry cells to update and this was very efficient and fast. Thanks again my friends. -- Firefighter Instructor "T. Valko" wrote: Create a 2 column table like this... A1:A57 = station number B1:B57 = district number It would look something like this... Station1...5 Station2...3 Station3...1 Station4...7 Station5...9 etc etc Then the formula would be... =VLOOKUP(H26,A1:B57,2,0) -- Biff Microsoft Excel MVP "Firefighter Instructor" wrote in message ... How do I create a formula that will return a number 1-10 depending on data entered ranging from 1-57. Example. I have 10 fire districts. Each district has about 5 Fire Stations in no particular order. If I put in fire station number 10 then the fire district 2 will auto fill-in. Here is my formula that i am trying to build but it's limited to a certain number I think 8. =IF(H26=3,1,IF(H26=4,1,IF(H26=6,1,IF(H26=18,1,IF(H 26=24,1,IF(H26=7,2,IF(H26=10,2,IF(H26=20,2,0)))))) )) Can I group numbers together in the formula such as Fire Stations 3, 4, 6, 18, or 24 = 1 7, 10, 13, 20 or 56 =2 8, 11, 17, 19 or 44 = 3 ..... = 4 ..... = 5 ..... = 6 ..... = 7 ..... = 8 ..... = 9 ..... = 10 Thanks very much! -- Firefighter Instructor . |
#5
|
|||
|
|||
Need help with Excel formula
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Firefighter Instructor" wrote in message ... Thanks very much! I was able to group them together, but had a limit of seven and I needed 10. The other method "VLOOKUP" was the way to go. I had around 1700 data entry cells to update and this was very efficient and fast. Thanks again my friends. -- Firefighter Instructor "T. Valko" wrote: Create a 2 column table like this... A1:A57 = station number B1:B57 = district number It would look something like this... Station1...5 Station2...3 Station3...1 Station4...7 Station5...9 etc etc Then the formula would be... =VLOOKUP(H26,A1:B57,2,0) -- Biff Microsoft Excel MVP "Firefighter Instructor" wrote in message ... How do I create a formula that will return a number 1-10 depending on data entered ranging from 1-57. Example. I have 10 fire districts. Each district has about 5 Fire Stations in no particular order. If I put in fire station number 10 then the fire district 2 will auto fill-in. Here is my formula that i am trying to build but it's limited to a certain number I think 8. =IF(H26=3,1,IF(H26=4,1,IF(H26=6,1,IF(H26=18,1,IF(H 26=24,1,IF(H26=7,2,IF(H26=10,2,IF(H26=20,2,0)))))) )) Can I group numbers together in the formula such as Fire Stations 3, 4, 6, 18, or 24 = 1 7, 10, 13, 20 or 56 =2 8, 11, 17, 19 or 44 = 3 ..... = 4 ..... = 5 ..... = 6 ..... = 7 ..... = 8 ..... = 9 ..... = 10 Thanks very much! -- Firefighter Instructor . |
Thread Tools | |
Display Modes | |
|
|