Jan 20 2019 03:05 PM
I want L2 to calculate what # it'll take to make K2 = 4000, K2 is a SUM
Jan 20 2019 03:51 PM
Jan 20 2019 04:07 PM
Damien thank you so much for trying to help! I'm stuck! Maybe this just can't be done. I tried what you suggested, but it came back and said, "There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly. Try removing or changing these references, or moving the formulas to different cells." :(
Jan 20 2019 04:22 PM
Hi @jennigetz
Did you put the formula into L2? If you have put it into K2 it will become circular.
Cheers
Damien
Jan 20 2019 04:35 PM
yes I tried it in L2
I think the problem is that K2 is already a sum
It's not the biggest deal in the world, I just have to manually enter #s into L2 until K2 gets close to 4000
G & I use L so I think that's the problem
I'm just not that familiar with Excel, I'm proud of myself for even getting this far :)
Thank you again for trying to help!
Jan 20 2019 04:41 PM
Hi @jennigetz
You are doing great and trial and error is part of the learning experience!
The example I gave was with K2 as a SUM and it worked OK (please see file attached).
What formula are you using in K2?
If you are able, it may be worthwhile to upload the file (remove any personal info) and I can have a quick squiz if you would like. Otherwise have a look at the file attached and see if that helps to pinpoint the issue.
Cheers
Damien
Jan 20 2019 06:26 PM
wow I really appreciate your time
I've attached my spreadsheet
I am a real estate broker and I am trying to help my buyers understand what they can afford in each neighborhood
Each has different HOA fees and taxes
This particular client wants to stay under $4,000/month
I think I've done everything right, I'd just like to not have to manually enter the purchase price amount in L
I'd like for L to be able to calculate itself based on everything else and keep K under $4,000
I hope this makes sense
If you have a minute to take a look I'd be very appreciative...
Jan 20 2019 07:54 PM
Hi @jennigetz
Ok I think I'm following your line of logic now.
The one thing I'm missing in my head is how you calculate $545,000 as the purchase price?
How do the numbers you have there equal to that? E.g. for K2 is this amount over a term of 12 years to arrive at the purchase limit?
Cheers
Damien
Jan 20 2019 07:58 PM
Hello,
The value in L2 is used in calculations in G2 and I2. I2 is used in the sum in K2. Therefore, you cannot reference K2 in a formula in L2, because that creates a circular reference.
I think what you want to achieve cannot be done with worksheet formulas alone, since it requires iteration. You may want to look into using add-ins like Solver.
Jan 20 2019 08:17 PM
I just made it up :)
I just started plugging in numbers in the L column until it hits $4,000 or less in the K column
That's the problem, that's what I want to avoid doing
It's time consuming to fill in all the other #s and then have to trial and error #s in L
But if I have to, I have to, it's not that big of a deal, I was just hoping there was something I was missing and a formula could be plugged in to do it for me
Thank you again!
Jan 20 2019 08:18 PM
This is what I was afraid of.
Thank you so much for your response
Jan 20 2019 08:34 PM
Hi @jennigetz
I've explored your spreadsheet a bit more and you should definitely stick with the current model and just trial and error the prices.
The problem other than the circular reference is that you also have other variables that are manually entered such as HOA, Special Assessment tax. Very hard to program a formula without knowing how these values are calculated.
Best wishes with your work and have a great week ahead!
Cheers
Damien