# Data VAlidation With If statement

1. ## 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.

2. ## Re: Data VAlidation With If statement

Hi!

See if this works for you.

Deep

3. ## Re: Data VAlidation With If statement

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

4. ## Re: Data VAlidation With If statement

see this attachment

5. ## Re: Data VAlidation With If statement

Ghozi Alkatiris formula is simple and straight.

You should go for that.

Deep

6. ## Re: Data VAlidation With If statement

Originally Posted by Ghozi Alkatiri
see this attachment
Thanks for the effort. But in your sheet installments doesn't adds up to total.

7. ## Re: Data VAlidation With If statement

Just change his formula to -

=IF(\$C6>COLUMNS(\$D:D)-1,\$A6/\$C6,"") in cell D6

Deep

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1