MHB How can I rearrange this complex formula in Excel to solve for G13?

[dipster307]

I an having problems trying arrange the formula below. I want to change it so the formula starts wuth "G13" equals to.

40981.10 = G13 - ( (((G13-B12-C8)*C3) + (B12*B3)) + (((C18-B9)*C5) + ((G13-C18)*C19)) )

The equation above is used in MS Excel, this is why you see the variable G13, B12 etc.
B12 = 34370
C8 = 8105
C3 = 0.4
B3 = 0.2
C18 = 42475
B9 = 7605
C5 = 0.12
C19 = 0.02
Can someone please help.

 

[Ackbach]

First step: distribute the minus sign to obtain

40981.10 = G13 - (((G13-B12-C8)*C3) + (B12*B3)) - (((C18-B9)*C5) + ((G13-C18)*C19)).

Next, you have to scan the equation for appearances of G13. Where are they? Do they multiply other numbers? If so, can you gather all those expression in one place?

 

[SuperSonic4]

I an having problems trying arrange the formula below. I want to change it so the formula starts wuth "G13" equals to.

40981.10 = G13 - ( (((G13-B12-C8)*C3) + (B12*B3)) + (((C18-B9)*C5) + ((G13-C18)*C19)) )

The equation above is used in MS Excel, this is why you see the variable G13, B12 etc.
B12 = 34370
C8 = 8105
C3 = 0.4
B3 = 0.2
C18 = 42475
B9 = 7605
C5 = 0.12
C19 = 0.02
Can someone please help.

Let's define some constants to make them easier to work with. You can define these in excel if you like instead of back subbing.

$\alpha = B12+C8$
$\beta = B12 \cdot B3$
$\gamma = (C18 - B9) \cdot C5$

In case you're wondering I have used an addition sign in $\alpha$ because G13 - B12-C8 = G13 - (B12+C8)[/color]

We can now sub in the constants we've defined above:
40981.10 = G13 -( (((G13 - $\alpha$ )*C3) + $\beta$ + ( $\gamma $ + ((G13-C18)*C19)) )

After distributing the minus sign and clearing up superfluous brackets:
40981.10 = G13 - (G13 - $\alpha$ )*C3 - $\beta$ - ( $\gamma $ + (G13-C18)*C19)You can distribute that minus sign in the last term too. From there it's a case of multiplying out and then combining those terms with G13 in them and those that don't.

 

[dipster307]

Let's define some constants to make them easier to work with. You can define these in excel if you like instead of back subbing.

$\alpha = B12+C8$
$\beta = B12 \cdot B3$
$\gamma = (C18 - B9) \cdot C5$

In case you're wondering I have used an addition sign in $\alpha$ because G13 - B12-C8 = G13 - (B12+C8)

We can now sub in the constants we've defined above:
40981.10 = G13 -( (((G13 - $\alpha$ )*C3) + $\beta$ + ( $\gamma $ + ((G13-C18)*C19)) )

After distributing the minus sign and clearing up superfluous brackets:
40981.10 = G13 - (G13 - $\alpha$ )*C3 - $\beta$ - ( $\gamma $ + (G13-C18)*C19)You can distribute that minus sign in the last term too. From there it's a case of multiplying out and then combining those terms with G13 in them and those that don't.

So the would the answer be:

40981.10 = G13 - (G13 - α )*C3 - β - ( γ + (G13-C18)*C19)

40981.10 = X

X = G13 – G13*C3 - α*C3 - β - γ + G13*C19 – C18*C19
X = G13 – G13*C3 + G13*C19 - α*C3 - β - γ – C18*C19
X = G13(1 – C3 + C19) - α*C3 - β - γ – C18*C19
X + α*C3 + β + γ + C18*C19 = G13(1 – C3 + C19)

(X + α*C3 + β + γ + C18*C19 ) / (1 – C3 + C19) = G13

However if I put the values in I get 71594.13 = G13.

The answer should be 58965.52, G13 must equal to 58965.52.
So where am I going wrong in the arrangement??

 

[SuperSonic4]

So the would the answer be:

40981.10 = G13 - (G13 - α )*C3 - β - ( γ + (G13-C18)*C19)

40981.10 = X

X = G13 – G13*C3 - α*C3 - β - γ + G13*C19 – C18*C19
X = G13 – G13*C3 + G13*C19 - α*C3 - β - γ – C18*C19
X = G13(1 – C3 + C19) - α*C3 - β - γ – C18*C19
X + α*C3 + β + γ + C18*C19 = G13(1 – C3 + C19)

(X + α*C3 + β + γ + C18*C19 ) / (1 – C3 + C19) = G13

However if I put the values in I get 71594.13 = G13.

The answer should be 58965.52, G13 must equal to 58965.52.
So where am I going wrong in the arrangement??

You've got some signs muddled up

40981.10 = G13 - (G13 - α )*C3 - β - ( γ + (G13-C18)*C19) -- original equation for reference.

X = G13 – G13*C3 -[/color] α*C3 - β - γ +[/color] G13*C19 –[/color] C18*C19

^ I've put in red where you have the wrong sign.

For the first one you're distributing the minus sign across both terms.: -C3 * - $\alpha$ = C3*$\alpha$ .. I find it helps if you either imagine/put C3 at the front or act like you're distributing a -1 where there is just a minus sign

The second and third ones are a little trickier to spot. I started by eliminating the bracket inside (G13-C18) by expansion before applying the minus sign outside the ($\gamma$ + (G13-C18)*C19) brackets:If we just concentrate on this bit: -($\gamma$ + (G13-C18)*C19)

Expanding out the inside brackets: -($\gamma$ + G13*C19 - C18*C19)

Now it's easier to distribute the minus sign: -$\gamma$ - G13*C19 + C18*C19 (because I am multiplying two negatives)Brought back into the equation as a whole: X = G13 – G13*C3 + $\alpha$*C3 - $\beta$ - $\gamma$ - G13*C19 + C18*C19.

The rest of goes as you worked out but with the sign changes and you end up with: G13 = (X - $\alpha$*C3 + $\beta$ + $\gamma$ - C18*C19 ) / (1 – C3 - C19)

Unfortunately I am about 0.3 out (I get 58965.21) yet I cannot spot where I went wrong but hopefully someone else will be able to see it.

For reference the values I took:
$\alpha = 42475$
$\beta = 6874$
$\gamma = 4184.4$
C3 = 0.4
C18 = 42745
C19 = 0.02

 

[Sudharaka]

I an having problems trying arrange the formula below. I want to change it so the formula starts wuth "G13" equals to.

40981.10 = G13 - ( (((G13-B12-C8)*C3) + (B12*B3)) + (((C18-B9)*C5) + ((G13-C18)*C19)) )

The equation above is used in MS Excel, this is why you see the variable G13, B12 etc.
B12 = 34370
C8 = 8105
C3 = 0.4
B3 = 0.2
C18 = 42475
B9 = 7605
C5 = 0.12
C19 = 0.02
Can someone please help.

Hi dipster307, :)

Here is the result obtained using Maxima.

\[G13=\frac{10\,C3\,C8+\left( 10\,B9-10\,C18\right) \,C5+10\,B12\,C3+10\,C18\,C19-10\,B12\,B3-409811}{10\,C3+10\,C19-10}\]

Kind Regards,
Sudharaka.

 

[Reckoner]

My working (open the spoiler if you want to look at a jumbled mess of variables):

\[40\,981.10 = \mathrm{G13} - \bigg[\Big[\big[(\mathrm{G13}-\mathrm{B12}-\mathrm{C8})\cdot\mathrm{C3}\big] + (\mathrm{B12}\cdot\mathrm{B3})\Big] + \Big[\big[(\mathrm{C18}-\mathrm{B9})\cdot\mathrm{C5}\big] + \big[(\mathrm{G13}-\mathrm{C18})\cdot\mathrm{C19}\big]\Big] \bigg]\]

Spoiler

\[\Rightarrow40\,981.10 = \mathrm{G13} - \Big[\big[(\mathrm{G13}-\mathrm{B12}-\mathrm{C8})\cdot\mathrm{C3}\big] + (\mathrm{B12}\cdot\mathrm{B3})\Big] - \Big[\big[(\mathrm{C18}-\mathrm{B9})\cdot\mathrm{C5}\big] + \big[(\mathrm{G13}-\mathrm{C18})\cdot\mathrm{C19}\big]\Big]\]
\[\Rightarrow40\,981.10 = \mathrm{G13} - \big[(\mathrm{G13}-\mathrm{B12}-\mathrm{C8})\cdot\mathrm{C3}\big] - (\mathrm{B12}\cdot\mathrm{B3}) - \big[(\mathrm{C18}-\mathrm{B9})\cdot\mathrm{C5}\big] - \big[(\mathrm{G13}-\mathrm{C18})\cdot\mathrm{C19}\big]\]
\[\Rightarrow40\,981.10 = \mathrm{G13} - \mathrm{G13}\cdot\mathrm{C3}+(\mathrm{B12}+ \mathrm{C8})\cdot\mathrm{C3} - (\mathrm{B12}\cdot\mathrm{B3}) - (\mathrm{C18}-\mathrm{B9})\cdot\mathrm{C5} - \mathrm{G13}\cdot\mathrm{C19}+\mathrm{C18}\cdot \mathrm{C19}\]
\[\Rightarrow40\,981.10 = \mathrm{G13} - \mathrm{G13} \cdot \mathrm{C3}+\mathrm{B12} \cdot \mathrm{C3} + \mathrm{C8}\cdot\mathrm{C3} - \mathrm{B12} \cdot \mathrm{B3} - \mathrm{C18} \cdot \mathrm{C5}+\mathrm{B9} \cdot \mathrm{C5} - \mathrm{G13} \cdot \mathrm{C19}+\mathrm{C18}\cdot \mathrm{C19}\]
\[\Rightarrow40\,981.10 = \mathrm{G13}(1 - \mathrm{C3} - \mathrm{C19})+\mathrm{B12} \cdot \mathrm{C3} + \mathrm{C8}\cdot\mathrm{C3} - \mathrm{B12} \cdot \mathrm{B3} - \mathrm{C18} \cdot \mathrm{C5}+\mathrm{B9} \cdot \mathrm{C5}+\mathrm{C18}\cdot \mathrm{C19}\]

\[\Rightarrow\mathrm{G13} = \frac{40\,981.10 - \mathrm{B12} \cdot \mathrm{C3} - \mathrm{C8}\cdot\mathrm{C3} + \mathrm{B12} \cdot \mathrm{B3} + \mathrm{C18} \cdot \mathrm{C5}-\mathrm{B9} \cdot \mathrm{C5}-\mathrm{C18}\cdot \mathrm{C19}}{1 - \mathrm{C3} - \mathrm{C19}}\]

And after comparing my solution with SuperSonic4's, they appear to be identical. And I get ~58965.52 in both cases, so I'm guessing that some number got entered incorrectly somewhere.

 

[dipster307]

My working (open the spoiler if you want to look at a jumbled mess of variables):

\[40\,981.10 = \mathrm{G13} - \bigg[\Big[\big[(\mathrm{G13}-\mathrm{B12}-\mathrm{C8})\cdot\mathrm{C3}\big] + (\mathrm{B12}\cdot\mathrm{B3})\Big] + \Big[\big[(\mathrm{C18}-\mathrm{B9})\cdot\mathrm{C5}\big] + \big[(\mathrm{G13}-\mathrm{C18})\cdot\mathrm{C19}\big]\Big] \bigg]\]

Spoiler

\[\Rightarrow40\,981.10 = \mathrm{G13} - \Big[\big[(\mathrm{G13}-\mathrm{B12}-\mathrm{C8})\cdot\mathrm{C3}\big] + (\mathrm{B12}\cdot\mathrm{B3})\Big] - \Big[\big[(\mathrm{C18}-\mathrm{B9})\cdot\mathrm{C5}\big] + \big[(\mathrm{G13}-\mathrm{C18})\cdot\mathrm{C19}\big]\Big]\]
\[\Rightarrow40\,981.10 = \mathrm{G13} - \big[(\mathrm{G13}-\mathrm{B12}-\mathrm{C8})\cdot\mathrm{C3}\big] - (\mathrm{B12}\cdot\mathrm{B3}) - \big[(\mathrm{C18}-\mathrm{B9})\cdot\mathrm{C5}\big] - \big[(\mathrm{G13}-\mathrm{C18})\cdot\mathrm{C19}\big]\]
\[\Rightarrow40\,981.10 = \mathrm{G13} - \mathrm{G13}\cdot\mathrm{C3}+(\mathrm{B12}+ \mathrm{C8})\cdot\mathrm{C3} - (\mathrm{B12}\cdot\mathrm{B3}) - (\mathrm{C18}-\mathrm{B9})\cdot\mathrm{C5} - \mathrm{G13}\cdot\mathrm{C19}+\mathrm{C18}\cdot \mathrm{C19}\]
\[\Rightarrow40\,981.10 = \mathrm{G13} - \mathrm{G13} \cdot \mathrm{C3}+\mathrm{B12} \cdot \mathrm{C3} + \mathrm{C8}\cdot\mathrm{C3} - \mathrm{B12} \cdot \mathrm{B3} - \mathrm{C18} \cdot \mathrm{C5}+\mathrm{B9} \cdot \mathrm{C5} - \mathrm{G13} \cdot \mathrm{C19}+\mathrm{C18}\cdot \mathrm{C19}\]
\[\Rightarrow40\,981.10 = \mathrm{G13}(1 - \mathrm{C3} - \mathrm{C19})+\mathrm{B12} \cdot \mathrm{C3} + \mathrm{C8}\cdot\mathrm{C3} - \mathrm{B12} \cdot \mathrm{B3} - \mathrm{C18} \cdot \mathrm{C5}+\mathrm{B9} \cdot \mathrm{C5}+\mathrm{C18}\cdot \mathrm{C19}\]

\[\Rightarrow\mathrm{G13} = \frac{40\,981.10 - \mathrm{B12} \cdot \mathrm{C3} - \mathrm{C8}\cdot\mathrm{C3} + \mathrm{B12} \cdot \mathrm{B3} + \mathrm{C18} \cdot \mathrm{C5}-\mathrm{B9} \cdot \mathrm{C5}-\mathrm{C18}\cdot \mathrm{C19}}{1 - \mathrm{C3} - \mathrm{C19}}\]

And after comparing my solution with SuperSonic4's, they appear to be identical. And I get ~58965.52 in both cases, so I'm guessing that some number got entered incorrectly somewhere.

Thanks everyone for helping out, I just need to keeping practicing my maths skills a bit more :)