How to include ranges of data in a nested EXCEL formula
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)))))))) |
How to include ranges of data in a nested EXCEL formula
=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)))))))) |
How to include ranges of data in a nested EXCEL formula
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)))))))) |
How to include ranges of data in a nested EXCEL formula
=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)))))))) |
All times are GMT +1. The time now is 01:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com