MS Excel Help - Auto calculate values in 3 cells if value changes in 4th cell

[Kratos47]

Hi,

I would appreciate some help regarding some auto calculation in excel.

What i'm attempting is get values automatically if i change values in a particular column and then subsequently, as a result, values in 3 other columns also gets changed.

Like:
Column 1 - Cell Value- 100
Column 2 - Cell Value- 20
Column 3 - Cell Value- 40
Column 4 - Cell Value- 60
If value in Column 1 value gets changed to 50, value in 2,3,4 automatically gets divided by 2.
Giving me this:
Column 1 - Cell Value- 50
Column 2 - Cell Value- 10
Column 3 - Cell Value- 20
Column 4 - Cell Value- 30

Is it possible somehow? Some googling tells me that it can be achieved using Macros. But I have no idea how all that works.

Can anyone help?

 

[mxnerd]

You don't need macros.

YouTube

Enjoy the videos and music you love, upload original content, and share it all with friends, family, and the world on YouTube.

www.youtube.com

 

[Kratos47]

@mxnerd
It shows me multiple videos, most of them are basic formulas which I already know.
Can you please show me the video tut for the exact problem I'm encountering?
Basically I want change in 3 columns or row values if the 4th adjacent one is divided or multiplied by some X value without having to do it manually.

 

[mxnerd]

Oops, misunderstood your question. But I feel sleepy.

Later.

 

[JJChicken]

I have two solutions depending on how the columns change their values.

A) If only one specific column changes values and each row in that column changes by the same multiplier, eg

100
50
20

becomes

50
25
10

Then you can create a new cell where you put in the multiplier change (0.5 here) and then have the other columns reference this multiplier cell change.

This avoids using macros but is limited in functionality.

B) If you want the change in values of the rows of the first columns to be independent of each other (see below) then I think you have to resort to Macros.

100
50
20

to

50
5
15

The reason you need macros is I’m 99.99995% sure there is no excel formula that can reference the previous value of a cell, after its been updated (which you need to calculate the multiplier).

So here’s what you do (for your example)

- Create a replica of column 1 (call it column 0)
- Column 0 will have the “original values” on first run and the “previous values” after update
- Have column 2-4 reference the changes made (i.e. column 1 / column 0 as a multiplier) — might be easier to split this by having a set of columns 2-4 which represent their original values and then another set of column 2-4 which = original value * multiplier
- Do a macro that copies across column 1 to column 0 when you need to update column 1 again (note that prior to any updates, column 0 = column 1 so the multiplier is just 1) — this macro is super easy, you should be able to google this or use the macro recorder (start macro recorder, select column 1, press copy, then paste special values onto column 0, press stop macro recorder)

This is a very basic example, you can add more complexity to it to fulfill your functionality. Hint: try and add additional columns and manipulate their values through macro / formulae similar to above

If you are struggling then let us know and we can help further.

All the best

 

[mxnerd]

This probably will help.

Have't wrote any macro/vba for a long time. Couldn't make it run on my machine though.