I am attempting to create a formula which links the number of patient beds in
a given hospital to the number of square feet needed in the Dietetics
department of that hospital. All the possible bed totals have been reduced
to seven outcomes: (from 1-100 beds should generate 6,650 square feet of
dietetics; 101-200 beds should generate 8,645; 201-300 should generate
10,640; 301-400 should generate 12,635; 401-500 should generate 14,630;
501-600 should generate 16,625 and 600-2000 should generate 18,620)

The sum of all beds is captured in cell D61 and the following attempt was
made to create the desired formula. It was unsuccessful. Wherein is my
syntax error?

=IF(D61=1:100,(6650),IF(D61=101:200,(8645),IF(D61= 201:300,(10640),IF(D61=301:400,(12635),IF(D61=401: 500,(14630),IF(D61=501:600,(16625),IF(D61=601:2000 ,(18620))))))))

=6650 + MIN( 6, INT( ( D61 - 1 ) / 100 ) ) * 1995
--
Regards,
Luc.

"Festina Lente"


"Jim McCord" wrote:

I am attempting to create a formula which links the number of patient beds in
a given hospital to the number of square feet needed in the Dietetics
department of that hospital. All the possible bed totals have been reduced
to seven outcomes: (from 1-100 beds should generate 6,650 square feet of
dietetics; 101-200 beds should generate 8,645; 201-300 should generate
10,640; 301-400 should generate 12,635; 401-500 should generate 14,630;
501-600 should generate 16,625 and 600-2000 should generate 18,620)

The sum of all beds is captured in cell D61 and the following attempt was
made to create the desired formula. It was unsuccessful. Wherein is my
syntax error?

=IF(D61=1:100,(6650),IF(D61=101:200,(8645),IF(D61= 201:300,(10640),IF(D61=301:400,(12635),IF(D61=401: 500,(14630),IF(D61=501:600,(16625),IF(D61=601:2000 ,(18620))))))))

0=IF(D61600,(18620),IF(D61500,(16625),IF(D61400 ,(14630),IF(D61300,(12635),IF(D61200,(10640),IF( D61100,(8645),IF(D610,(6650),0)))))))
based on your formula, less than 1 bed indicates 0. You may also need to
include a round statement to the nearest whole number.
Lou


"Jim McCord" wrote:

I am attempting to create a formula which links the number of patient beds in
a given hospital to the number of square feet needed in the Dietetics
department of that hospital. All the possible bed totals have been reduced
to seven outcomes: (from 1-100 beds should generate 6,650 square feet of
dietetics; 101-200 beds should generate 8,645; 201-300 should generate
10,640; 301-400 should generate 12,635; 401-500 should generate 14,630;
501-600 should generate 16,625 and 600-2000 should generate 18,620)

The sum of all beds is captured in cell D61 and the following attempt was
made to create the desired formula. It was unsuccessful. Wherein is my
syntax error?

=IF(D61=1:100,(6650),IF(D61=101:200,(8645),IF(D61= 201:300,(10640),IF(D61=301:400,(12635),IF(D61=401: 500,(14630),IF(D61=501:600,(16625),IF(D61=601:2000 ,(18620))))))))

=IF(D61600,(18620),IF(D61500,(16625),IF(D61400, (14630),IF(D61300,(12635),IF(D61200,(10640),IF(D 61100,(8645),IF(D610,(6650),0)))))))
based on your formula, less than 1 bed indicates 0. You may also need to
include a round statement to the nearest whole number.
Lou
don't know where the 0 originated

"Rookie 1st class" wrote:

0=IF(D61600,(18620),IF(D61500,(16625),IF(D61400 ,(14630),IF(D61300,(12635),IF(D61200,(10640),IF( D61100,(8645),IF(D610,(6650),0)))))))
based on your formula, less than 1 bed indicates 0. You may also need to
include a round statement to the nearest whole number.
Lou


"Jim McCord" wrote:

I am attempting to create a formula which links the number of patient beds in
a given hospital to the number of square feet needed in the Dietetics
department of that hospital. All the possible bed totals have been reduced
to seven outcomes: (from 1-100 beds should generate 6,650 square feet of
dietetics; 101-200 beds should generate 8,645; 201-300 should generate
10,640; 301-400 should generate 12,635; 401-500 should generate 14,630;
501-600 should generate 16,625 and 600-2000 should generate 18,620)

The sum of all beds is captured in cell D61 and the following attempt was
made to create the desired formula. It was unsuccessful. Wherein is my
syntax error?

=IF(D61=1:100,(6650),IF(D61=101:200,(8645),IF(D61= 201:300,(10640),IF(D61=301:400,(12635),IF(D61=401: 500,(14630),IF(D61=501:600,(16625),IF(D61=601:2000 ,(18620))))))))