Reverse Excel formula please

[Bowraven]

I have a formula that I want to reverse, as follows:

Cell A1 = 7
Cell A2 = 4*
Cell A3 = £150
Cell A4 = 100
Cell A5 = 50
Cell A6 = 5%

*Note: Cell A2 is a function of Cell A1, which is an average (i.e. (7+6+5+4+3+2+1)/7); so for example if Cell A1 = 6, then cell A2 would be (6+5+4+3+2+1)/6 = 3.5 and so on.

Total sales =

Cell A7 = Cell A1 (7) x Cell A3 (£150) x Cell A4 (100) x (1- Cell A6 (5%)=95%) = £99,750
Cell A8 = Cell A2 (4) x Cell A3 (£150) x Cell A5 (50) = £30,000

Total of Cell A7 and Cell A8 (which is in Cell A9) = £129,750


So now I would like two formulas:

A formula to take total per Cell A9 and get back to cell A1, i.e. 7

A formula to take total per Cell A9 and get back to cell A2, i.e. 4

Thank you, Russell

 

[Special-K99]

Just divide.

A8=(A9-A7) / (A2*A5)
A7=(A9-A8) / ((A3*A4*(1-A6))

 

[Bowraven]

Hi Special, thank you for your help - the second line works.

 

[Bowraven]

Hi again Special-K99 - actually this formula doesn't work, let me explain why.

If you are given the total £129,700, but you don't know what either Cell A1 and Cell A2 are, so therefore you don't know either A7 or A8, you just have the total of £129,700, is there a way to get to the 7 in Cell A1 and the 4 in Cell A2?

So to be clear, the information you do have is as follows:

Cell A1 = Unknown
Cell A2 = Unknown, but is a function of A1 as an average (i.e. (7+6+5+4+3+2+1)/7)
Cell A3 = £150
Cell A4 = 100
Cell A5 = 50
Cell A6 = 5%
Cell A9 = £129,750

Thank you, Russell

 

[Marcelo Branco]

If i understand correctly you have two equations
A1*A3*A4*(1-A6) + A2*A3*A5 = A9
2*A2 -A1 = 1

So, try something like this

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Equations​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Coefficients of A1​

[/TD]
[TD]

Coefficients of A2​

[/TD]
[TD]

Sum​

[/TD]
[TD]

Variables​

[/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

150​

[/TD]
[TD][/TD]
[TD]

14250​

[/TD]
[TD]

7500​

[/TD]
[TD]

129750​

[/TD]
[TD]

A1=​

[/TD]
[TD="bgcolor: #D9D9D9"]

7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

100​

[/TD]
[TD][/TD]
[TD]

-1​

[/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

A2=​

[/TD]
[TD="bgcolor: #D9D9D9"]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

50​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

5%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

129750​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formulas
C3
=A3*A4*(1-A6)
C4
-1
D3
=A3*A5
D4
2
E3
=A9
E4
1

Then select G3:G4 (gray area) and in the formula bar type this array formula
=MMULT(MINVERSE(C3:D4),E3:E4)
confirm with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[Bowraven]

Hi Marcelo,

I have no idea what that formula means, but it works like a treat, thank you so much!

Regards, Russell

 

[Marcelo Branco]

Hi Russel

You are welcome. Glad to help

It is the way to solve systems of first degree equations. Very useful!

M.

 

[Bowraven]

Hi again Marcelo - another scenario that I'd like the update to this formula please:

I have a formula that I want to reverse, as follows:

Cell A1 = 7
Cell A2 = 4*
Cell A3 = £150
Cell A4 = 100
Cell A5 = 50
Cell A6 = 5%

*Note: Cell A2 is a function of Cell A1, which is an average (i.e. (7+6+5+4+3+2+1)/7); so for example if Cell A1 = 6, then cell A2 would be (6+5+4+3+2+1)/6 = 3.5 and so on.

Total sales =

Cell A7 = Cell A1 (7) x Cell A3 (£150) x Cell A4 (100) = £105,000
Cell A8 = Cell A2 (4) x Cell A3 (£150) x Cell A5 (50) = £30,000
Less: Cell A9 =-Cell A4 (100) x Cell A3 (£150) x Cell A6 (5%) x Cell A2 (4) = -£3,000

Net total of Cell A7 to Cell A9 (which is in Cell A10) = £132,000

So now I would like two formulas:

A formula to take total per Cell A10 and get back to cell A1, i.e. 7

A formula to take total per Cell A10 and get back to cell A2, i.e. 4

Thank you, Russell

 

[Marcelo Branco]

Now the two equations are
=A1*A3*A4 + A2(A3*A5 - A3*A4*A6) = A10
=2*A2 - A1 = 1

All you have to do is to adjust the formulas (coefficients and sum)

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Equations​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Coefficients of A1​

[/TD]
[TD]

Coefficients of A2​

[/TD]
[TD]

Sum​

[/TD]
[TD]

Variables​

[/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

150​

[/TD]
[TD][/TD]
[TD]

15000​

[/TD]
[TD]

6750​

[/TD]
[TD]

132000​

[/TD]
[TD]

A1=​

[/TD]
[TD="bgcolor: #D9D9D9"]

7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

100​

[/TD]
[TD][/TD]
[TD]

-1​

[/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

A2=​

[/TD]
[TD="bgcolor: #D9D9D9"]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

50​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

5%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

132000​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


C3
=A3*A4
C4
-1
D3
=A3*A5-A3*A4*A6
D4
2
E3
=A10
E4
1

Same array formula in G3:G4

M.

 

[Bowraven]

Thank you once again Marcelo for your kind help, very much appreciated.

Have a fab Christmas and Happy New Year to you.

Cheers, Russell