Ok got a homework problem to do and have no clue how to do this since it's not cover in the book but the class requires you to know how to use excel.
I am thinking to use the sumifs function for this. Here's the hw problem:
Some company does a match on the donations each employee gives out. The company decides to do a 50% match but has a stipulation of $2,000 max for each employee.
So, I am given a excel file. It has a table of info of the employees first and last name, the organization they donated to and the amount. This table shows the transactions. So, the employee can make x many donations to any organization. However, the next page I need to know how much in total each organization gets.
So, lets say there's 6 different organizations. I need to know the total donations given to that organization and the avarage amounts.
However, the rules is that each employee has a limit of $2,000 for that mathchig. So, the company would double the amount that they donated to the max of $2,000 So, if one employee gave $4,000 to the organization then the company will only give out $4,000 max because it doubles what was given at a max of $2,000 So, the company will only pay a max of 4,000 for each employee.
However, the table isn't neat and it shows the same employee about donating 2 or 3 times but there's many different emploees. I need to make sure I will only double each employees donation by max of $2,000 if they paid anything about $2,000 I won't double it but still would add it in to the total.
So, for each non profit organization. I need to figure out the total. I need to scan the table and if a employee donates more than one time. I need to check to make sure they didn't reach the $2,000 limit. If they did then the I only add in the max of $4,000.
How should I go about it ? I one have a single output box and I cannot type in the answers. I have to use formulas to give the total results.
So, for each employee that donates the company will match it meaning double it. The max that it will double is $2,000 which means the company will add a max of $4,000 the most for each employee. So, that limits each employee that the company will give that organization $4,000 the max.
The table has a lot of employees but I need to write a single formula that will give me the total for each individual none profit organization.
How do I do this? How do I use the formulas to put a limit of $2,000?
here's the logic:
employee1 donations <= $2,000 take value and times it by 50% and add that back in. to the total for non profit organization 1 and then add in anything excess of the limit but don't double that amount.
This is what I need to do to get a total. So, for each employee I will only double their donations if their total amount is less than $2,000 anything above that doesn't get match but still add that back into the total. I want the result to show the total donations for that none profit organization. The company will match the employees donations as long each employee's donation is $2,000 or less So the max is $2,000 meaning the company will add in $4,000 for each emploeey that's if each employee donates $2,000 but anything above that amount the company won't match it. Yet, still that ammount would be added in.
How would I do this? I so far am using the sumsifs function and it's just grabbing all the employees that made donations to organization 1 and totally them. I don't have anything to check if every employee violated that $2,000 limite and didn't double their donations yet.
I am stuck at the part as to how to check if each employee's amount is $2,000 or less then apply that double amount to increase their donations by double. If they do pass that $2,000 amount. I then will only add $4,000 to what they invested.
how do I do this?
I am thinking to use the sumifs function for this. Here's the hw problem:
Some company does a match on the donations each employee gives out. The company decides to do a 50% match but has a stipulation of $2,000 max for each employee.
So, I am given a excel file. It has a table of info of the employees first and last name, the organization they donated to and the amount. This table shows the transactions. So, the employee can make x many donations to any organization. However, the next page I need to know how much in total each organization gets.
So, lets say there's 6 different organizations. I need to know the total donations given to that organization and the avarage amounts.
However, the rules is that each employee has a limit of $2,000 for that mathchig. So, the company would double the amount that they donated to the max of $2,000 So, if one employee gave $4,000 to the organization then the company will only give out $4,000 max because it doubles what was given at a max of $2,000 So, the company will only pay a max of 4,000 for each employee.
However, the table isn't neat and it shows the same employee about donating 2 or 3 times but there's many different emploees. I need to make sure I will only double each employees donation by max of $2,000 if they paid anything about $2,000 I won't double it but still would add it in to the total.
So, for each non profit organization. I need to figure out the total. I need to scan the table and if a employee donates more than one time. I need to check to make sure they didn't reach the $2,000 limit. If they did then the I only add in the max of $4,000.
How should I go about it ? I one have a single output box and I cannot type in the answers. I have to use formulas to give the total results.
So, for each employee that donates the company will match it meaning double it. The max that it will double is $2,000 which means the company will add a max of $4,000 the most for each employee. So, that limits each employee that the company will give that organization $4,000 the max.
The table has a lot of employees but I need to write a single formula that will give me the total for each individual none profit organization.
How do I do this? How do I use the formulas to put a limit of $2,000?
here's the logic:
employee1 donations <= $2,000 take value and times it by 50% and add that back in. to the total for non profit organization 1 and then add in anything excess of the limit but don't double that amount.
This is what I need to do to get a total. So, for each employee I will only double their donations if their total amount is less than $2,000 anything above that doesn't get match but still add that back into the total. I want the result to show the total donations for that none profit organization. The company will match the employees donations as long each employee's donation is $2,000 or less So the max is $2,000 meaning the company will add in $4,000 for each emploeey that's if each employee donates $2,000 but anything above that amount the company won't match it. Yet, still that ammount would be added in.
How would I do this? I so far am using the sumsifs function and it's just grabbing all the employees that made donations to organization 1 and totally them. I don't have anything to check if every employee violated that $2,000 limite and didn't double their donations yet.
I am stuck at the part as to how to check if each employee's amount is $2,000 or less then apply that double amount to increase their donations by double. If they do pass that $2,000 amount. I then will only add $4,000 to what they invested.
how do I do this?
