Hi,
I need to make a reverse calculation based on a few formulas like below.
I have this datas starting with A1:
A B C D E F G
1 gross som cas cass deduct imp net
2 2200 11 231 121 320 243 1594
Formulas:
B2 = A2*0.5%
C2 = A2*10.5%
D2 = A2*5.5%
E = 320 (fixed value for all)
F = (A2-B2-C2-D2-E2)*16%
G2 = A2-B2-C2-D2-F2
What i need is to put a value in G3 (ex: 2200) and to find the values in A3, B3, C3, D3, E3, F3.
Can this be done?
Thank you kindly,
Puiuluipui
Please upload a sample file
Don't forget to rate 1 who helped u, using "Star"
Hi,
I need formulas where ther is a question mark.
Thank you very much.
Do you think it's possible to make it work?
Thank you kindly,
Puiuluipui
Sorry mate, You have got too many unknowns. You could approach with Goal Seek but not sure
if I can provide you a formula that would work.
i think it is doable but what is the deduct amount, is that fixed (you have no formula for it)?
EDIT: does it even factor in to the final amount? it doesn't appear to.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Hi,
Yes, the deduct is the fixed 320 value.
The 320 value, along others is used for the imp value.
Thank you.
is this your homework?
Something like this, but i'm just trying to help someone.
Thanks.
Pl see attached file
see the attached file
Looks like you got your answer. It isn't against forum rules to help someone with homework but it defeats the purpose of homework to have others do it for you. I was just going to give you this and tell you to solve for A3 (hoping it is an accurate reflection of the formulas that went into your result)
a3=2200+[(a3)-{(a3)*(.005)}+{(a3)*(.105)}+{(a3)*(.055)}+{a3-{((a3)*(.005))-((a3)*(.105))-((a3)*(.055))-320}*.16}]
and finish with a hint that your value in cell A3 is 3063.
I was working on this, in the background, while everyone else was producing their solutions ... so you might as well have it:
B2: =A2*0.5%
C2: =A2*10.5%
D2: =A2*5.5%
E2: 320
F2: =(A2*(1-0.5%-10.5%-5.5%)-E2)*16%
G2: =(A2*(1-0.5%-10.5%-5.5%))*(1 - 16%) + E2*16%
H2: =(G2-E2*16%)/(1 - 16%)/(1-0.5%-10.5%-5.5%)
G2=(A2*(1-0.5%-10.5%-5.5%))*(1 - 16%) + E2*16%
G2-E2*16% = (A2*(1-0.5%-10.5%-5.5%))*(1 - 16%)
(G2-E2*16%)/(1 - 16%) = (A2*(1-0.5%-10.5%-5.5%))
(G2-E2*16%)/(1 - 16%) = A2*(1-0.5%-10.5%-5.5%)
(G2-E2*16%)/(1 - 16%)/(1-0.5%-10.5%-5.5%) = A2
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Thank you all for your help. it's working great!
Sambo kid, you are right. I will do as you sugested. Great idea.
I also have the final formula from everybody who helped me, to check the progress.
Thank you all again.
You're welcome. Thanks for the rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks