Data VAlidation With If statement

Consider i have an amount e.g. 100,000 in A1. Now this amount can be divided equally on 24 months or 36 months i.e. 100,000/24 or 100,000/36.

I want to create formula that if i select 2 years in validation column( "A2" ) it Automatically divide the A1 value by relevant no of months and map the result monthly from January to December in appropriate columns. I have attached a sample file of what i want.

Please have a look.

I would suggest having input boxes for the year, but having the number format for it (ctrl+1) being Custom: 0 "years". That way you just enter a whole number like "3" and it will say "3 years". Then you can make calculations with it—e.g. derive the number of months as being "x years" * 12. Then I'd have a start date that you want. Then make a bunch of if statements based on the number of columns across the cell is at to determine if it's ≤ the number of months… if so, put it down, otherwise make it "". A similar function would determine the payment.

I've attached a file to this which shows what I'm getting at.

Book1-editedbyScott.xlsx

-Scott

see this attachment

Originally Posted by Ghozi Alkatiri

see this attachment

Thanks for the effort. But in your sheet installments doesn't adds up to total.