Formula calculates in cells according to a criteria

[el_ja]

Hello
I'm not quite sure how to explain it.
I want a simple formula of payments, in this example $100 in 5 months means $20 a month.
But I don't want to be adding formulas every time I change the months for example

[TABLE="width: 500"]
<tbody>[TR]
[TD]Value[/TD]
[TD]100[/TD]
[TD][/TD]
[TD]Months[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]+10/5[/TD]
[TD]+10/5[/TD]
[TD]+10/5[/TD]
[TD]+10/5[/TD]
[TD]+10/5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If I want to do 8 months now it would be
[TABLE="width: 500"]
<tbody>[TR]
[TD]Value[/TD]
[TD]100[/TD]
[TD][/TD]
[TD]Months[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]+10/6[/TD]
[TD]+10/6[/TD]
[TD]+10/6[/TD]
[TD]+10/6[/TD]
[TD]+10/6[/TD]
[TD]+10/6[/TD]
[TD]+10/6[/TD]
[TD]+10/6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I want to have a formula in all 12 columns all the time, but it only shows a value in any number of months from the criteria and 0 in the remaining months.
I'm thinking it should be done with an array, but I don't even know how to start searching.
Thanks for the help.

 

[jtakw]

Hi,

If your data is set up just like your sample where the months are just numbers, then you can do this, however, I don't understand why you're dividing 10 rather then 100, and by 6 rather than 8 for 8 months:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Value	100		Months	8
2
3	1	2	3	4	5	6	7	8	9	10	11	12
4	12.5	12.5	12.5	12.5	12.5	12.5	12.5
Sheet50

Cell Formulas
Range	Formula
A4	=IF(A3<$E1,$B1/$E1,"")

A4 formula copied across to month 12 or more as needed.

 

[el_ja]

Sorry, my mistake (and can't find the edit button)... I started thinking one thing and writing another.
I must be 100 instead of 10, and 8 instead of 6.

The formula worked great, I was overthinking it too much.
Thanks a lot!

 

[jtakw]

You're welcome, glad you got it to work.