formula help
 

The spreadsheet has 7 columns. For each horizontal row, the columns will
have a number of hours manually entered into all or only some of the 7 cells
in that row. In the case where not all of the cells have numbers, then those
empty cells will just be blank. I need help creating a formual in Excel that
will do the following:

For each row, total all of the numbers listed in the 7 cells, let's call
that total X. The formula will first divide X by 8. To understand the
forumula I need, let's call the resulting number to the left of decimal point
Y, and the number to the right of the decimal point Z.

--if Y is equal to or exceeds the total number of cells with number entries,
then the answer will be the total number of cells with number entries.

--if Y is less than the total number cells with number entries, then the
answer will be equal to:

Y plus 0.5, if Z is less than or equal to 0.5 or 1, if Z is greater than
0.5)

Thank you so much for any help you can provide.

try
=MIN(COUNT(A1:A7),CEILING(SUM(A1:A7)/8,0.5))

"ToddG" wrote:

The spreadsheet has 7 columns. For each horizontal row, the columns will
have a number of hours manually entered into all or only some of the 7 cells
in that row. In the case where not all of the cells have numbers, then those
empty cells will just be blank. I need help creating a formual in Excel that
will do the following:

For each row, total all of the numbers listed in the 7 cells, let's call
that total X. The formula will first divide X by 8. To understand the
forumula I need, let's call the resulting number to the left of decimal point
Y, and the number to the right of the decimal point Z.

--if Y is equal to or exceeds the total number of cells with number entries,
then the answer will be the total number of cells with number entries.

--if Y is less than the total number cells with number entries, then the
answer will be equal to:

Y plus 0.5, if Z is less than or equal to 0.5 or 1, if Z is greater than
0.5)

Thank you so much for any help you can provide.

That's perfect. Many Thanks!

"bj" wrote:

try
=MIN(COUNT(A1:A7),CEILING(SUM(A1:A7)/8,0.5))

"ToddG" wrote:

The spreadsheet has 7 columns. For each horizontal row, the columns will
have a number of hours manually entered into all or only some of the 7 cells
in that row. In the case where not all of the cells have numbers, then those
empty cells will just be blank. I need help creating a formual in Excel that
will do the following:

For each row, total all of the numbers listed in the 7 cells, let's call
that total X. The formula will first divide X by 8. To understand the
forumula I need, let's call the resulting number to the left of decimal point
Y, and the number to the right of the decimal point Z.

--if Y is equal to or exceeds the total number of cells with number entries,
then the answer will be the total number of cells with number entries.

--if Y is less than the total number cells with number entries, then the
answer will be equal to:

Y plus 0.5, if Z is less than or equal to 0.5 or 1, if Z is greater than
0.5)

Thank you so much for any help you can provide.