Use number of gaps between cells in a column to perform calculation

[jamesladbrooke1]

Hi,
This is my first time posting to mrexcel so please bear with me! I've been tearing my hair out over what is probably an easily solvable question which is why I've come here! I basically have a table like the one below- in the left hand column are numbers (representing amounts of money), and there are variable gaps between each cell going down the column. I basically want to create a formula that will somehow calculate how many gaps there are between each cell and use this number to perform a simple division which will output a number in the right hand column. I will use the first entry (360) as an example. What I'd like is 60 in each cell on the right, and this is calculated from number of rows +1 (to include the 360 itself) that I want to divide 360 by. I've completed the next two entries down the column (75 and 140) but have left the rest uncalculated, as they are on my table. At the moment I'm eyeballing how many rows there are and dividing the number in the left hand column by that number to get each entry in the right hand column. Hopefully somebody can make some sort of sense from a combination of my terrible explanation/table below illustrating what I'd like formularised to help me!
Many thanks in advance,
Jamie
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]360[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]75[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]140[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD]240[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]95[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Nishant94]

Assuming your data starts from A4 and you have data till A1000, then use this:

=IF(A4<>"",A4/SMALL(IF(A5:$A$1000<>"",ROW(A5:$A$1000)-ROW(A4)),1),C3)

As this is an array formula it requires ctrl+shift+enter after putting the formula. Just pressing "Enter" wont do the job.

 

[jamesladbrooke1]

Thank you so much, this has worked absolutely perfectly!! Thank you also for replying so swiftly. Could I possibly be a pain and ask if you might be able to help me on another, similar request (to do with calculating something based on gaps in a column etc...). Can I ask you on this thread or should I open another new thread?
Many thanks,
Jamie

 

[Nishant94]

You should open a new thread. There are very good members in this forum who will definitely help you.

 

[jamesladbrooke1]

Okay great, many thanks. Have just opened a new thread with this query.

 

[jamesladbrooke1]

Hi Nishant94, so I started using this code and it seemed to be working brilliantly at first, then I started having a few problems with it and I've tried replicating it on some simple test data and it's completely stopped working- I feel like I'm going crazy as it definitely worked at first and I have an original column of data outputted by it to prove it. Cannot for the life of me work out what has changed?! I'm making sure that I'm pressing Ctrl+shift+enter, and even using test data starting in A4 and copying the code exactly, it no longer works. Do you have any suggestions?
Thanks in advance,
Jamie