Ace_Quared
New Member
- Joined
- May 24, 2022
- Messages
- 4
- Office Version
- 2021
- Platform
- Windows
Hi,
I'm struggling with setting up a formula that will limit the sum of a value to a plural of the fraction it is created from.
Let me explain with an example:
The system receives fractions that represent worked hours/total hours needed to attain a FTE. Each line represents a different activity, the entire table represents all the activities from one employee and is sorted as such that the best paid hours are on top (which will be considered first to complete the FTE).
To Calculate the overall FTE for an employee we need to achieve a total of min. 10000 (each fraction is calculated and multiplied by 10000 to eliminate decimals). Although the max of 10000 is not strictly enforced because an employee can only perform a full hour (or 1/denominator of said line). Therefore the total is limited to maximum 9999 + ((1/denominator of the last selected hour)*10000). Now comes the complexity, the 9999 I used to define the maximum value of my total is not always 9999....
In the example below I will have to use 5833 + ONLY (12/27)*10000 = 10277 (I can only use 12 out of the 15 hours to achieve my FTE)
If I use 11/27 I do not reach 10000 and if I use 13/27 I would be over my limit of 9999+((1/denominator of the last selected hour)*10000)
FYI: The hours that were not allocated to the FTE will be allocated in another system.
Is there a way to setup my formulas in excel to achieve this automatically (excel that picks the right numerator to achieve more than 10000 but less than max limit across my whole column "allocate to FTE" and returns empty value in underlying cells once max tot value at the bottom is reached?
My apologies if the explanation is not explicit enough
Thanks in advance,
Kr
I'm struggling with setting up a formula that will limit the sum of a value to a plural of the fraction it is created from.
Let me explain with an example:
The system receives fractions that represent worked hours/total hours needed to attain a FTE. Each line represents a different activity, the entire table represents all the activities from one employee and is sorted as such that the best paid hours are on top (which will be considered first to complete the FTE).
To Calculate the overall FTE for an employee we need to achieve a total of min. 10000 (each fraction is calculated and multiplied by 10000 to eliminate decimals). Although the max of 10000 is not strictly enforced because an employee can only perform a full hour (or 1/denominator of said line). Therefore the total is limited to maximum 9999 + ((1/denominator of the last selected hour)*10000). Now comes the complexity, the 9999 I used to define the maximum value of my total is not always 9999....
In the example below I will have to use 5833 + ONLY (12/27)*10000 = 10277 (I can only use 12 out of the 15 hours to achieve my FTE)
If I use 11/27 I do not reach 10000 and if I use 13/27 I would be over my limit of 9999+((1/denominator of the last selected hour)*10000)
FYI: The hours that were not allocated to the FTE will be allocated in another system.
| Denominator | Numerator | N/D*10000 | Allocate to FTE |
24 | 14 | 5833 | 5833 |
27 | 15 | 5556 | (12/27)*10000=4444 |
25 | 7 | 2800 | |
10277 |
Is there a way to setup my formulas in excel to achieve this automatically (excel that picks the right numerator to achieve more than 10000 but less than max limit across my whole column "allocate to FTE" and returns empty value in underlying cells once max tot value at the bottom is reached?
My apologies if the explanation is not explicit enough
Thanks in advance,
Kr
