Pro rata question

Cossie

Active Member
Joined
May 6, 2002
Messages
328
I am looking for a way to calculate monthly and annual pro rata so that it automatically adjusts to the current day and month and year

eg September has 30 days and on the 15th day of the month the prorata is 50%, 10th day of september is 33% etc but when it changes to october i would like it to calcualte based on 31 days etc

for the annual pro rata i am looking for the same thing so that it calcualtes in any given year the prorata of the current date.

Is a variation of networkdays the answer?

hope this makes sense and thank you
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
This?


Excel 2010
ABC
1Datemonthyear
22/25/201989.29%15.34%
33/9/201929.03%18.63%
43/25/201980.65%23.01%
55/1/20193.23%33.15%
66/1/20193.33%41.64%
77/7/201922.58%51.51%
88/11/201935.48%61.10%
99/22/201973.33%72.60%
1010/3/20199.68%75.62%
1111/5/201916.67%84.66%
1211/7/201923.33%85.21%
1311/11/201936.67%86.30%
1411/18/201960.00%88.22%
1512/17/201954.84%96.16%
Sheet3
Cell Formulas
RangeFormula
B2=DAY(A2)/DAY(EOMONTH(A2,0))
C2=YEARFRAC(DATE(YEAR(A2),1,1)-1,A2,1)
 
Upvote 0
if i get this as the result how do i remove the training numbers and end up with 92.8%

=CONCATENATE("Pro Rata ",($K$2*100)) is my formula

Pro Rata 92.8571428571429 is what is displayed

thanks again
 
Upvote 0
=CONCATENATE("Pro Rata ",(trunc($K$2*100,1)),"%")



you can also join strings with & instead of concatenate
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top