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

 

[Marcelo Branco]

Russel,

You are very welcome.

Merry Christmas and Happy New Year



M.

 

[Bowraven]

I have another variation of the same set of numbers to resolve please:

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'd like two formulas please:

1. To get back to Cell A4 (i.e. 100) as if I didn't know it, but knew the £132,000 as above, which is made up with the above formula and numbers.
2. To get back to Cell A5 (i.e. 50) as if I didn't know it, but knew the £132,000 as above, which is made up with the above formula and numbers.

I hope that makes sense and thank you in advance.

Russell

 

[Marcelo Branco]

Russel,

Think about it. This case is very simple
From the first equation: A7 =A1*A3*A4 , comes
A4 = A7/(A1*A3) = 105000/(7*150) = 105000/1050 = 100

From the second equation: A8 = A2*A3*A5, comes
A5 = A8/(A2*A3) = 30000/(4*150) = 30000/600 = 50

M.

 

[Marcelo Branco]

Russel,

Thoughts
You probably have lots of scenarios to deal with. You'd better try to solve them by yourself - so you will not need help in the future.

For more complex cases (not like the last one,that it's very simple), first of all, try to create the two equations that make up the system - it only requires basic knowledge of algebra.

Then, by observing the two equations, change the formulas or values of the coefficients of A1 and A2 in C3:D4. Include the values on the right-hand side of each equation in E3:E4.

Exactly like I did in the two first cases.

If all is done correctly, the formulas in G3:G4 should present the desired results.

If, after some tries, you are in trouble don't hesitate for asking for help.

M.

 

[Bowraven]

Russel,

Think about it. This case is very simple
From the first equation: A7 =A1*A3*A4 , comes
A4 = A7/(A1*A3) = 105000/(7*150) = 105000/1050 = 100

From the second equation: A8 = A2*A3*A5, comes
A5 = A8/(A2*A3) = 30000/(4*150) = 30000/600 = 50

M.

Hi Marcelo - before I post a question I spend time trying to work these out - for this one, I'd already spent at least 2 hours this morning trying to work it out - Algebra wasn't my strong subject at school! LOL

In the end I had worked out the second formula before your post and was about to post this when I saw you'd answered, so thank you for that.

However, the first one, i.e. getting back to the 100 is still unsolved, i.e. it's getting back from the £132,000 to 100, or actually getting back from £102,000 (i.e. £132,000-£30,000), which is net of the £3,000.

Since I posted this, I have been trying to work it out myself too (with a hospital visit in between!), I just get brain block, so sorry and I really appreciate your help.

Anyway, so far I have:

((A10) £132,000-(A8)£30,000+£3,000)/(A3) £150* (A1) 7) = 100

Which is similar to yours, except it's starting from £132,000.

The problem I can't get my head around is how to get to the £3,000 as this involves the 100 in the first place to get to.

Thank you once again.

Cheers, Russell

P.s. this is the last one actually - I think LOL

 

[Marcelo Branco]

I thought A7 and A8 are given.
It they are unknown we are in trouble, because there is only one equation with two unknowns (in red), that is
A4*A1*A3 + A5*A2*A3 - A4*A2*A3*A6 = 132000
that can be reduced to
A4*7*150 + A5*4*150 - A4*4*150*0.05 = 132000
1050*A4 + 600*A5 - 30*A4 = 132000
1020*A4 + 600*A5 = 132000

This equation has an infinite number of solutions, for example
A4=100 and A5=50
A4=0 and A5=220
etc
etc

To have an unique solution you need to create another equation that relates A4 to A5. Just for example
A4 = 2*A5
or
A4 = 50 + A5

M.

 

[Bowraven]

Hi Marcelo - there's only one unknown in this, as we know A5 = 50

So to be clear, what we do know is:

Cell A1 = 7
Cell A2 = 4 - which is a function of A1
Cell A3 = £150
Cell A5 = 50
Cell A6 = 5%
Cell A10 = £132,000

So we are now looking for cell A4, which in this case we know equals 100, but it's the formula to get back to it.

Does that help, or is this an impossible equation?

 

[Marcelo Branco]

Well, if A5 is known (50) the solution is very simple
1020*A4 + 600*A5 = 132000
Substituting A5 by its value
1020*A4 + 600*50 = 132000
1020*A4 = 132000 - 30000
A4 = 102000/1020
A4=100

M.

 

[Marcelo Branco]

In Excel

[Table="class: grid"][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][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

7​

[/td][td][/td][td]

Variable​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

4​

[/td][td][/td][td]

A4 =​

[/td][td]

100​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

150​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

50​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

5%​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/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][/tr]
[/table]

Formula in D2
=(A10-A2*A3*A5)/(A1*A3-A2*A3*A6)

M.

 

[Bowraven]

Hi Marcelo - I have just managed to work this one out also myself by trial and error - thank you so much for your help these last two days though, Cheers, Russell