Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF & VLOOKUP FORMULA | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
revert formula insertion to old method | Setting up and Configuration of Excel | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions | |||
Help with macro formula and variable | Excel Worksheet Functions |