I need to set some validation to a cell which contains formula.
The formula is:
=$F$2+(C9*$B$20*$C$3)+(D9*$B$20)
which basically transfers to:
= base allocation + (number of category A staff * Amount of money per Staff * weight) + (number of category B staff * amount of money per staff)
The spreadsheet basically calculates how much money four departments get out of £100,000 depending on how many of each staff they have. The category A staff get a certain times more money than category B staff, and this is defined by the weight. For example, if the weight is 3 the category A staff get 3 times more money than the category B staff.
The amount of money per staff was worked out by taking the base allocation times four (number of departments) and taking that away from the total allocation (£100,000). So for example if the base allocation is four you would be left with £60,000. Then by adding the number of category B staff to the number of category A staff * weight to give the total number of staff. So if there was 2 category B staff and 3 category A staff and the weight was 3 the total number of staff would be 2 + (3*3), therefore 11. You would then divide £60,000 by 11 giving the amount of money per staff.
What i need to do is set it so that one of the departments has a minimum allocation of £32,000, without adding anything to the total allocation of £100,000.
I have no idea how to do this. any suggestions?
The formula is:
=$F$2+(C9*$B$20*$C$3)+(D9*$B$20)
which basically transfers to:
= base allocation + (number of category A staff * Amount of money per Staff * weight) + (number of category B staff * amount of money per staff)
The spreadsheet basically calculates how much money four departments get out of £100,000 depending on how many of each staff they have. The category A staff get a certain times more money than category B staff, and this is defined by the weight. For example, if the weight is 3 the category A staff get 3 times more money than the category B staff.
The amount of money per staff was worked out by taking the base allocation times four (number of departments) and taking that away from the total allocation (£100,000). So for example if the base allocation is four you would be left with £60,000. Then by adding the number of category B staff to the number of category A staff * weight to give the total number of staff. So if there was 2 category B staff and 3 category A staff and the weight was 3 the total number of staff would be 2 + (3*3), therefore 11. You would then divide £60,000 by 11 giving the amount of money per staff.
What i need to do is set it so that one of the departments has a minimum allocation of £32,000, without adding anything to the total allocation of £100,000.
I have no idea how to do this. any suggestions?
