Formula Needed - Create a Multiplier w/o helper columns

[JayB0730]

Hello,

I am trying to automate, through an Excel formula, the ability to automatically deduct an amount based on another column.

• I have a date that determines the month in A1. Let's assume it is 8/1/2023 to represent the month of September.
• In B1, I have the amount of $236.
• In cell C1, I am trying to write a formula that considers the following:

1. Calculate an appropriate multiplier based on Today() = the month's end date listed in A1 (i.e., 8/31). For example, 8/31 = 1, 9/30 = 2, 10/31= 3, 11/30 = 4, 12/31=5, 1/31 =6
2. Additionally, we would want to split B1 into 6 to get = 39.33
3. Multiply this amount by each multiplier so that we get the following results: End of August = 39.33 (multiplier = 1), End of September = 78.67 (multiplier = 2) ... through End of January = $236
4. End of February - End of July = multiplier of 0.

TIA!

JayB

 

[Muhammad_Usman]

Hello,

I am trying to automate, through an Excel formula, the ability to automatically deduct an amount based on another column.

• I have a date that determines the month in A1. Let's assume it is 8/1/2023 to represent the month of September.
• In B1, I have the amount of $236.
• In cell C1, I am trying to write a formula that considers the following:

1. Calculate an appropriate multiplier based on Today() = the month's end date listed in A1 (i.e., 8/31). For example, 8/31 = 1, 9/30 = 2, 10/31= 3, 11/30 = 4, 12/31=5, 1/31 =6
2. Additionally, we would want to split B1 into 6 to get = 39.33
3. Multiply this amount by each multiplier so that we get the following results: End of August = 39.33 (multiplier = 1), End of September = 78.67 (multiplier = 2) ... through End of January = $236
4. End of February - End of July = multiplier of 0.

TIA!

JayB

First of all how 8/1/2023 represent month of September instead of august?
2nd You want Month wise Total or total sum in one cell ?

Share a Picture of your data with input and out put manually for batter understanding

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Create a Multiplier w/o helper columns
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.