SOLVED

# Solve for X with Functions

Copper Contributor

# Solve for X with Functions

Hello everyone,

I have a spreadsheet that I am trying to automate as much as allowed by policies. The issue I currently have could be solved with a Macro, Goal Seek, and even Solver, but we need something that is automatic and does not use any of those 3 tools. Hence, why I'm asking for help building a function instead.

For the spreadsheet I have attached, here is an explanation of each component:

Cost, On Hand, Adjustment, and Pending: Manual entries. Don't need help with those.

Factor 1 and Factor 2: Constant values that only get changed when C-Suites give the order.

Variable: The one entry that I need to play around with in order to get the end result I need.

Cash Total: The end result I need, which needs to be >= 0.

Deficit: This is the calculated cell that tells me what I need to enter in Variable to make Cash Total >= 0. It is the cell that I need help with writing a function for it for whenever Cash Total is a negative value.

To calculate Cash Total, the equation would be as follows:

(On Hand + Variable - Adjustment) * (1 + Factor 1) * Cost + Factor 2

I appreciate any help provided for this as I'm at my wits end.

best response confirmed by EngKnox (Copper Contributor)
Solution

# Re: Solve for X with Functions

So I think you just need a formula for column G to calculate what Variable (column E) needs to be to reach \$0 cash total?:
=C2-B2-\$M\$4/(1+\$J\$4)/A2
1 best response

Accepted Solutions
best response confirmed by EngKnox (Copper Contributor)
Solution

# Re: Solve for X with Functions

So I think you just need a formula for column G to calculate what Variable (column E) needs to be to reach \$0 cash total?:
=C2-B2-\$M\$4/(1+\$J\$4)/A2