Excel Forum

Hi Dear
I need your help for allocation of prepayment over a specified period and Conditional formatting the amount base on drop down list.
For example
C2
amount :2000
D2
Jan-Dec
E2:number of months
12
From F1 thru Q1
Jan thru Dec
How to write and Excel formula to allocate amount equally base on number of months.if not equal amount left should be in last period in this example Dec.
Once more say Drop down list contain Jan thru Dec.
If I choose Jan the amount in Jan should be highlighted.if Feb amount from Jan to Feb highlighted.Mar amount from Jan to Mar highlighted.so highlight from Jan onward.


Hope Guru here can help


Best wishes,

Could you please attach the example file for easy to understand and answer the question?

Hi Dear
I just upload the file the highlight cell need to be calculated.I put expected results as a sample.after doing right calculation,If I choose cell A1 Jan the amount in Jan highlight.if I choose Feb amount from Jan to Feb highlight and so on.to do this I want to mark those amount already cleared.

Hope you may help

Best wishes

Try this,

A4

=SUM(F3:INDEX(F3:Q3,MATCH(A1,F2:Q2,0))

Enter

For conditional formatting,

Select F3:Q3 > Menu Home > Conditional Formatting > New rule > Use a formula to determine which cells to format > In the Format values where this formula is true: enter this formula =COLUMNS($F3:F3)<=MATCH($A$1,$F$2:$Q$2,0) > Click format button and set your format > OK > OK

Thanks for your input,this works well.
Once more how to allocate amount from F3:Q3 without manual input and term calculation?

Best regards,

Could you please provide an example and attach the updated file again?

Hi Dear
For example amount 2000$
Term from 1/1/2020 To 12/31/2020
So term 12 months I use Datedif(start,end,"m") but not working fine.
so amount to allocate 2000/12=166.67
166.67*11=1833.37 so the last amount not 166.67 it would be 166.63(2000-1833.37)

Hope you may help

Try this,

1. D3
   =DATEDIF(B3-1,C3,"m")
   Enter
2. F2 fill in with 1/1/2020
3. G2
   =EDATE(F2,1)
   Enter and Copy to right
4. F3
   =IF(AND(EOMONTH($B3,0)<=EOMONTH(F$2,0),EOMONTH($C3,0)>EOMONTH(F$2,0)),ROUND($E3/$D3,2),IF(EOMONTH($C3,0)=EOMONTH(F$2,0),$E3-SUM($E3:E3)+$E3,0))
   Enter and Copy to right

Thanks it's working