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

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

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

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


.

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


.