May 18 2023 04:00 PM
May 18 2023 04:00 PM
I am building a spreadsheet to calculate projected revenue for a service based on calculation per person. Cost per person is based on age brackets, so I have a table for this to use vlookup. However, I would like to limit the amount of paying people to 4 per family, so if we have a family of 6, I will only count the first 4 members of the family.
Each of our members is assigned a 6-digit family number and a 2-digit person number, so I have an export from our system that lists each person in a row with their family number, person number, and the age bracket they're in.
Is there a way to disregard (not count) the rows that include any family member >4?
Here's the table with the cost per age bracket:
Here's the table that lists each person in a row with their Family number, Person number, and their age bracket, with the "Cost" column using a vlookup to the "Cost per Age Bracket" table. Highlighted in red are people >4 in a family (person 05 and person 06).
Is it possible to total the "Cost" column, but NOT count any of the rows >4 per family?
May 18 2023 10:20 PM
@bryson8420 Why not use an IF formula for Cost that sets the cost to zero when the person number is either 05 or 06? The you don't have to exclude them from the total.
May 19 2023 02:06 AMSolution
@bryson8420 You achieve your result by two way. You can use SUMIFS() to get total cost by applying condition <=4. Try-
Or apply a condition when you are looking up values against age. Try-
Or condition used to person number.
May 19 2023 12:24 PM
@Harun24HR Thanks for your help!! I do see that either of them will work, but I am going to use the second method you proposed so I can replace the hard-coded "4" in the if statement to reference a certain cell value so we can change that cell value to manipulate the calculations:
There's a second item I can't figure out, and was thinking I may be able to figure it out with the answer to my first item, but I still can't quite work it out:
Our services are similar to an health insurance company, where each person has a deductible before we begin paying on their medical bills. However, we have an individual deductible (per person), but then also have a family deductible, which is typically 2x the individual deductible. Once one of the deductibles is met, we will begin paying out a certain percentage of medical bills, say 90%. We want to be able to manipulate the numbers for individual deductible, family deductible, and percent of payout to show projected costs.
For example, the individual deductible is $1,000 and the family deductible is $2,000.
So the formula needs to look at how many people in the family:
Is this possible?? I've updated the file you sent me with this additional info. Any help you can give is greatly appreciated!!