Help to calculate Drinking Fountains

[Codeman]

Excel formula help needed to calculate Drinking Fountains.

Total fixture count is say 7 (cells B5 divided by F8 on my spreadsheet
rounded up)
One cell (F18) is to show 50% of the total fixture count rounded up which
would be 4.
Another cell (F19) is to show the answer of say 7 – 4 = 3. F19’s answer is
never to be less than 1.

The numerical values in the cells change with the occupant load and the
numbers provided are for reference only.

Cell F18 formula is: =SUM(ROUNDUP((B5/F8)*50%,0)) This formula provides the
total fixture count from cells B5÷F8 and then takes 50% of the answer and
rounds up.

Cell F19 is the cell I am trying to complete. Its answer should never be
less than 1 and F19’s answer added to F18’s answer should be always equal the
Total fixture count (B5÷F8 rounded up). However, if the total fixture count
is only 1 then F19 should also have a 1. F18 will always have at least 1 as
an answer as will the total fixture count.

Cell F19 will be the required wheel chair accessible drinking fountains.
You always have to have one low drinking fountain and one higher drinking
fountain even if only one drinking fountain is required for the occupant load.

Cell F18 will be the required high drinking fountains.

Can anyone help me with the formula to be in Cell F19?

 

[Tom Hutchins]

Does this give the results you want?

In F18: =ROUNDUP((B5/F8)*50%,0)
In F19: =MAX(ROUNDUP((B5/F8),0)-F18,1)

To prevent errors in case F8 is zero, try

In F18: =IF(F8=0,1,ROUNDUP((B5/F8)*50%,0))
In F19: =IF(F8=0,1,MAX(ROUNDUP((B5/F8),0)-F18,1))

Hope this helps,

Hutch

 

[Codeman]

Thank you Tom I used the first two formulas you listed. Cell F8 will never
be 0.