I need help on completing a formula in excel.

Problem:

The resulting answer must account for an egress width answer based on a
known occupant load and number of Exits.
With an occupant load of 49 or less the egress width cannot be less than
36-inches and the number of exits would be one or two.
With an occupant load of 50 or more the egress width must be 44-inches
minimum but could be more and the number of exits would be two or more.

Egress width is occupant load times egress factor all divided by number of
Exits. The egress factor, occupant loads and number of Exits will be in
cells on the spread sheet. The egress width in inches will require rounding
up to the next full number (no decimals). I have developed the formula so
far as rounding up but I need to modify the formula to the limits mentioned
in the first paragraph above.

Examples:
(49 x 0.20)/2 = 4.9 (rounded up the value is 5). However, the minimum must
be 36-inches.
(312 x 0.20)/2 = 31.2 (rounded up the value is 32). However, the minimum
must be 44-inches since the occupant load is 50 or more.
(958 x 0.20)/3 = 63.9 (rounded up the value is 64). The minimum of 44 is
exceeded and the calculated value of 64 must be used.

Cell G5 - Occupant load; 312-people
Cell C47 - Egress Factor; 0.20
Cell H5 - Number of Exits; 2
The formula I have so far is below;

=ROUNDUP(((G5*C47)/H5),1)

I did find one thing in my formula that I also need correction. On the
round up function I need the round up to always be one if the resultant
answer is less than 1. But that may not bean issue if the 36 inches is
worked in the formula as a minimum.

(2 x 0.15)/1 = 0.3 comes out as 0 using my formula. However, if the
occupant load is 3 then the answer rounds to 1. 0.15 is another egress
factor I can use in buildings with a sprinkler system.

Is there a way to make the formula never be below 36 when the occupant load
is 49 or less, never be below 44 when the occupant load is 50 or more and
when the occupant load is large enough to require the egress width to be
larger than 44 it will show the values above 44?

Any help would be appreciated.

Codeman

Although it might seem a bit odd at first, the formula:

=MAX(36,your_formula)

will produce 36 if your_formula is less than 36, and so it will
effectively set 36 as the minimum that can be returned, even though
MAX is used.

An alternative approach is:

=IF(your_formula<36,36,your_formula)

Hope this helps (for now).

Pete

On Oct 1, 6:59*pm, Codeman wrote:
I need help on completing a formula in excel.

Problem:

The resulting answer must account for an egress width answer based on a
known occupant load and number of Exits.
With an occupant load of 49 or less the egress width cannot be less than
36-inches and the number of exits would be one or two.
With an occupant load of 50 or more the egress width must be 44-inches
minimum but could be more and the number of exits would be two or more.

Egress width is occupant load times egress factor all divided by number of
Exits. *The egress factor, occupant loads and number of Exits will be in
cells on the spread sheet. *The egress width in inches will require rounding
up to the next full number (no decimals). *I have developed the formula so
far as rounding up but I need to modify the formula to the limits mentioned
in the first paragraph above.

Examples:
(49 x 0.20)/2 = 4.9 (rounded up the value is 5). *However, the minimum must
be 36-inches.
(312 x 0.20)/2 = 31.2 (rounded up the value is 32). *However, the minimum
must be 44-inches since the occupant load is 50 or more.
(958 x 0.20)/3 = 63.9 (rounded up the value is 64). *The minimum of 44 is
exceeded and the calculated value of 64 must be used.

Cell G5 - Occupant load; 312-people
Cell C47 - Egress Factor; 0.20
Cell H5 - Number of Exits; 2
The formula I have so far is below;

=ROUNDUP(((G5*C47)/H5),1)

I did find one thing in my formula that I also need correction. *On the
round up function I need the round up to always be one if the resultant
answer is less than 1. *But that may not bean issue if the 36 inches is
worked in the formula as a minimum.

(2 x 0.15)/1 = 0.3 comes out as 0 using my formula. *However, if the
occupant load is 3 then the answer rounds to 1. *0.15 is another egress
factor I can use in buildings with a sprinkler system.

Is there a way to make the formula never be below 36 when the occupant load
is 49 or less, never be below 44 when the occupant load is 50 or more and
when the occupant load is large enough to require the egress width to be
larger than 44 it will show the values above 44?

Any help would be appreciated.

Codeman

This seem to work, if not it will give you some ideas
=MIN(MAX(ROUND(G5*0.2/H5,0),IF(G5<50,36,0),IF(G5=50,44,0)),64)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Codeman" wrote in message
...
I need help on completing a formula in excel.

Problem:

The resulting answer must account for an egress width answer based on a
known occupant load and number of Exits.
With an occupant load of 49 or less the egress width cannot be less than
36-inches and the number of exits would be one or two.
With an occupant load of 50 or more the egress width must be 44-inches
minimum but could be more and the number of exits would be two or more.

Egress width is occupant load times egress factor all divided by number of
Exits. The egress factor, occupant loads and number of Exits will be in
cells on the spread sheet. The egress width in inches will require
rounding
up to the next full number (no decimals). I have developed the formula so
far as rounding up but I need to modify the formula to the limits
mentioned
in the first paragraph above.

Examples:
(49 x 0.20)/2 = 4.9 (rounded up the value is 5). However, the minimum
must
be 36-inches.
(312 x 0.20)/2 = 31.2 (rounded up the value is 32). However, the minimum
must be 44-inches since the occupant load is 50 or more.
(958 x 0.20)/3 = 63.9 (rounded up the value is 64). The minimum of 44 is
exceeded and the calculated value of 64 must be used.

Cell G5 - Occupant load; 312-people
Cell C47 - Egress Factor; 0.20
Cell H5 - Number of Exits; 2
The formula I have so far is below;

=ROUNDUP(((G5*C47)/H5),1)

I did find one thing in my formula that I also need correction. On the
round up function I need the round up to always be one if the resultant
answer is less than 1. But that may not bean issue if the 36 inches is
worked in the formula as a minimum.

(2 x 0.15)/1 = 0.3 comes out as 0 using my formula. However, if the
occupant load is 3 then the answer rounds to 1. 0.15 is another egress
factor I can use in buildings with a sprinkler system.

Is there a way to make the formula never be below 36 when the occupant
load
is 49 or less, never be below 44 when the occupant load is 50 or more and
when the occupant load is large enough to require the egress width to be
larger than 44 it will show the values above 44?

Any help would be appreciated.

Codeman

Hi,

I have used the following cell address for my example:
Cell A1 - Occupant load; 312-people
Cell A2 - Egress Factor; 0.20
Cell A3 - Number of Exits; 2

then this should do the trick:

=MAX(IF(A1<=49,36,44),ROUNDUP(A1*A2/A3,1))



--
Thanks,
Shane Devenshire


"Codeman" wrote:

I need help on completing a formula in excel.

Problem:

The resulting answer must account for an egress width answer based on a
known occupant load and number of Exits.
With an occupant load of 49 or less the egress width cannot be less than
36-inches and the number of exits would be one or two.
With an occupant load of 50 or more the egress width must be 44-inches
minimum but could be more and the number of exits would be two or more.

Egress width is occupant load times egress factor all divided by number of
Exits. The egress factor, occupant loads and number of Exits will be in
cells on the spread sheet. The egress width in inches will require rounding
up to the next full number (no decimals). I have developed the formula so
far as rounding up but I need to modify the formula to the limits mentioned
in the first paragraph above.

Examples:
( 49 x 0.20)/2 = 4.9 (rounded up the value is 5). However, the minimum must
be 36-inches.
(312 x 0.20)/2 = 31.2 (rounded up the value is 32). However, the minimum
must be 44-inches since the occupant load is 50 or more.
(958 x 0.20)/3 = 63.9 (rounded up the value is 64). The minimum of 44 is
exceeded and the calculated value of 64 must be used.

Cell G5 - Occupant load; 312-people
Cell C47 - Egress Factor; 0.20
Cell H5 - Number of Exits; 2
The formula I have so far is below;

=ROUNDUP(((G5*C47)/H5),1)

I did find one thing in my formula that I also need correction. On the
round up function I need the round up to always be one if the resultant
answer is less than 1. But that may not bean issue if the 36 inches is
worked in the formula as a minimum.

(2 x 0.15)/1 = 0.3 comes out as 0 using my formula. However, if the
occupant load is 3 then the answer rounds to 1. 0.15 is another egress
factor I can use in buildings with a sprinkler system.

Is there a way to make the formula never be below 36 when the occupant load
is 49 or less, never be below 44 when the occupant load is 50 or more and
when the occupant load is large enough to require the egress width to be
larger than 44 it will show the values above 44?

Any help would be appreciated.

Codeman

I used your equation and all seems to work fine.
Thank you for your assitance.

"ShaneDevenshire" wrote:

Hi,

I have used the following cell address for my example:
Cell A1 - Occupant load; 312-people
Cell A2 - Egress Factor; 0.20
Cell A3 - Number of Exits; 2

then this should do the trick:

=MAX(IF(A1<=49,36,44),ROUNDUP(A1*A2/A3,1))



--
Thanks,
Shane Devenshire


"Codeman" wrote:

I need help on completing a formula in excel.

Problem:

The resulting answer must account for an egress width answer based on a
known occupant load and number of Exits.
With an occupant load of 49 or less the egress width cannot be less than
36-inches and the number of exits would be one or two.
With an occupant load of 50 or more the egress width must be 44-inches
minimum but could be more and the number of exits would be two or more.

Egress width is occupant load times egress factor all divided by number of
Exits. The egress factor, occupant loads and number of Exits will be in
cells on the spread sheet. The egress width in inches will require rounding
up to the next full number (no decimals). I have developed the formula so
far as rounding up but I need to modify the formula to the limits mentioned
in the first paragraph above.

Examples:
( 49 x 0.20)/2 = 4.9 (rounded up the value is 5). However, the minimum must
be 36-inches.
(312 x 0.20)/2 = 31.2 (rounded up the value is 32). However, the minimum
must be 44-inches since the occupant load is 50 or more.
(958 x 0.20)/3 = 63.9 (rounded up the value is 64). The minimum of 44 is
exceeded and the calculated value of 64 must be used.

Cell G5 - Occupant load; 312-people
Cell C47 - Egress Factor; 0.20
Cell H5 - Number of Exits; 2
The formula I have so far is below;

=ROUNDUP(((G5*C47)/H5),1)

I did find one thing in my formula that I also need correction. On the
round up function I need the round up to always be one if the resultant
answer is less than 1. But that may not bean issue if the 36 inches is
worked in the formula as a minimum.

(2 x 0.15)/1 = 0.3 comes out as 0 using my formula. However, if the
occupant load is 3 then the answer rounds to 1. 0.15 is another egress
factor I can use in buildings with a sprinkler system.

Is there a way to make the formula never be below 36 when the occupant load
is 49 or less, never be below 44 when the occupant load is 50 or more and
when the occupant load is large enough to require the egress width to be
larger than 44 it will show the values above 44?

Any help would be appreciated.

Codeman

I received several answers and I used one of them. Thank you all for comming
to my aid.

"Codeman" wrote:

I need help on completing a formula in excel.

Problem:

The resulting answer must account for an egress width answer based on a
known occupant load and number of Exits.
With an occupant load of 49 or less the egress width cannot be less than
36-inches and the number of exits would be one or two.
With an occupant load of 50 or more the egress width must be 44-inches
minimum but could be more and the number of exits would be two or more.

Egress width is occupant load times egress factor all divided by number of
Exits. The egress factor, occupant loads and number of Exits will be in
cells on the spread sheet. The egress width in inches will require rounding
up to the next full number (no decimals). I have developed the formula so
far as rounding up but I need to modify the formula to the limits mentioned
in the first paragraph above.

Examples:
(49 x 0.20)/2 = 4.9 (rounded up the value is 5). However, the minimum must
be 36-inches.
(312 x 0.20)/2 = 31.2 (rounded up the value is 32). However, the minimum
must be 44-inches since the occupant load is 50 or more.
(958 x 0.20)/3 = 63.9 (rounded up the value is 64). The minimum of 44 is
exceeded and the calculated value of 64 must be used.

Cell G5 - Occupant load; 312-people
Cell C47 - Egress Factor; 0.20
Cell H5 - Number of Exits; 2
The formula I have so far is below;

=ROUNDUP(((G5*C47)/H5),1)

I did find one thing in my formula that I also need correction. On the
round up function I need the round up to always be one if the resultant
answer is less than 1. But that may not bean issue if the 36 inches is
worked in the formula as a minimum.

(2 x 0.15)/1 = 0.3 comes out as 0 using my formula. However, if the
occupant load is 3 then the answer rounds to 1. 0.15 is another egress
factor I can use in buildings with a sprinkler system.

Is there a way to make the formula never be below 36 when the occupant load
is 49 or less, never be below 44 when the occupant load is 50 or more and
when the occupant load is large enough to require the egress width to be
larger than 44 it will show the values above 44?

Any help would be appreciated.

Codeman