Lioness227
New Member
 Joined
 Mar 5, 2021
 Messages
 8
 Office Version

 2010
 Platform

 MacOS
Hi,
I am confused as to which formula would be more appropriate for the below problem I am trying to resolve.
2020 monthly payment tab
I have a list of participants who give £3 per month and can select a product. They can stop participating at any time.
If product A, £1 is allocated to product A budget. And 1 cent is allocated to 53 other products budgets (1 cent each).
Same rule apply to each product.
2020 one off payment tab
I have a list of participants who give whatever they want, for example £100 in December, as a one off.
1% of the £100 will be allocated to each product budget on the month the £100 have been given, so in this case £1 each in December.
last tab where formulating each product budget I have for each month.
=if(and(‘2021Monthly payment’!C3:C1000=“Product A”,’2020Monthly payment”!D3:D:1000=3), A60*sum(‘2020Monthly payment”!D3:D1000), A59*sum(‘2020Monthly payment”!D3:D1000))+(B62*’2020one off payment’!C2:C1000)
Data
A59= 0.0033333333
A60=0.33333334
B62=0.01
C3:C1000 is the column where Product name is written
D3:D1000 is the range of monthly participation’s £3 on each line
C2:C1000 is the range of one off payment
QUESTION
Shall I actually use another formula and I am completely wrong using if/and formula? Shall I look at pivot table? I can’t think anymore, been on this one for too long to find au automatic solution rather than doing it manually!
Many thanks in advance (and thankfully it is clear enough, if not please let me know).
I am confused as to which formula would be more appropriate for the below problem I am trying to resolve.
2020 monthly payment tab
I have a list of participants who give £3 per month and can select a product. They can stop participating at any time.
If product A, £1 is allocated to product A budget. And 1 cent is allocated to 53 other products budgets (1 cent each).
Same rule apply to each product.
2020 one off payment tab
I have a list of participants who give whatever they want, for example £100 in December, as a one off.
1% of the £100 will be allocated to each product budget on the month the £100 have been given, so in this case £1 each in December.
last tab where formulating each product budget I have for each month.
=if(and(‘2021Monthly payment’!C3:C1000=“Product A”,’2020Monthly payment”!D3:D:1000=3), A60*sum(‘2020Monthly payment”!D3:D1000), A59*sum(‘2020Monthly payment”!D3:D1000))+(B62*’2020one off payment’!C2:C1000)
Data
A59= 0.0033333333
A60=0.33333334
B62=0.01
C3:C1000 is the column where Product name is written
D3:D1000 is the range of monthly participation’s £3 on each line
C2:C1000 is the range of one off payment
QUESTION
Shall I actually use another formula and I am completely wrong using if/and formula? Shall I look at pivot table? I can’t think anymore, been on this one for too long to find au automatic solution rather than doing it manually!
Many thanks in advance (and thankfully it is clear enough, if not please let me know).