Different possible combination using division formula function within a column

[hellohello1]

Hi,

I have a question involving using the division formula. Attached is the picture of what I am trying to do.

So basically, C , D, E, F are columns and the right side is what I want to do.

So for person A, I want to do many possible divisions of the cells in that column. So as shown in the picture, the light blue means [All cell]/[first cell], and dark blue means [all cells]/[second cell] and continue on until the last cell in column E. Then from there, it will be inverse division e.g.[first cell]/[all cells], [second cell]/[all cells] and so on. So it is like doing all possible combination of division.

So next column will be the same as the previous column, where you do all possible division in Column F.

The problem here is, I have massive of data (thousands of values and Person ID), so it is impossible to do it manually as I have to keep changing the $ sign and letter, as well as dragging down all the way to the last cell which can be at Row 1000++ . Is there any other way to do it?

I know a bit of VBA and vb.net, so if there is a formula to cater this...I greatly appreciate it!

[MarvinP]

Hello hellohello and welcome to the forum.

If you arrange your data like the attached you can do a single formula and pull it over and down.

Copy the data down a single column and then copy and paste using TRANSPOSE to get it across the top row.

Then a single formula will work for the single person.

[hellohello1]

Hi Marvin,

Thanks for the reply. I have tried that but I have too many person ID in order to do that, and I will need to arrange as how I arrange in my picture because I have to do additional steps of calculation from there like finding SD and CV across all person ID. And I am also looking into doing it automated since its repetitive tasks since I have many files as well. Is there other solution to it?

[MarvinP]

OK -

Look at this with an Indirect formula. It might be another way to do your problem. If you have a lot of rows of data I'd suggest you do the problem like this... Attached.

[event21]

Hi -

You can start with this;

Please Login or Register  to view this content.

event

[kvsrinivasamurthy]

Pl see attached file with formula in H8 ,dragged across.

Please Login or Register  to view this content.

[hellohello1]

Hi Marvin,

Thanks. I have thought of that too...so far I think this is more ideal though it is harder to process for the next step. Thanks once again for your help

[hellohello1]

Pl see attached file with formula in H8 ,dragged across.

Please Login or Register  to view this content.

I am unable to open your file though

Hi -

You can start with this;

Please Login or Register  to view this content.

event

I couldn't run it. May I know how the code suppose to work(pardon my beginner knowledge in VBA)?

[event21]

Hi -

here is you wrote

I know a bit of VBA and vb.net

Hit Alt + F11, from the left side, right click and insert module then paste the code to the window that will open

event

[kvsrinivasamurthy]

Paste this in H8 and drag To I8 And downwards.

Please Login or Register  to view this content.

[hellohello1]

Paste this in H8 and drag To I8 And downwards.

Please Login or Register  to view this content.

Thanks! Will try it out!

Hi -

here is you wrote


Hit Alt + F11, from the left side, right click and insert module then paste the code to the window that will open

event

My Bad...forgot to change the value. Oh wow. Awesome... Thanks everyone for your help!