Solved: IF Formula problems

[Glaswegian]

Hi

I have a sheet with two input cells - one for years and the other for months. The figures in these two cells are then multipied by a number chosen from a dropdown to calculate a total number of payments. For example, if the term is 5 years and 0 months and payments will be made monthly, then the total number of payments will be 60. The number of payments possible in a year are

1
2
3
4
6
12

If a user inputs a number of months, I need to take account of this when calculating the total number of payments. I also need to take account of the number of payments per year. For example, if payments are quarterly (4 per year) and the total term is 4 years 5 months, then the total number of payments should be

4 x 4 = 16 + one payment over the 5 month period
=17

Needless to say my formula attempts are not quite working

=ROUNDDOWN(IF(E14=1,0,IF(AND(E14=2,G12=6),1,IF(AND(E14=3,G12<=9),G12/(E14+1),IF(AND(E14=3,G12>9),G12/(E14+1),IF(AND(E14=4,G12<=8),G12/(E14-1),IF(AND(E14=4,G12>8),G12/(E14-1),IF((E14=6),1,))))))),0)

E14 is the dropdown with the available number of payments, and G12 is the number of months.


I'd appreciate any help.

Thanks.

[GTO]

Greetings,

I am not much with formulas/worksheet functions, but I was thinking that if:

A2= Years
B2= Additional months
C2= Payments per year

Then:

=ROUNDUP(((A2*12)+B2)/(12/C2),0)

Dos that work?

Mark

[Glaswegian]

Hi Mark

Thanks for your reply - that works very nicely! I guess simplicity is the key...

I went for ROUNDDOWN rather than ROUNDUP, but apart from that - superb!

Many thanks.

[GTO]

You are certainly most welcome, and shucks, we both can celebrate as I was not exaggerating my skills at formula construction...

Have a great day!

Mark