Excel formula help

[jennigetz]

I want L2 to calculate what # it'll take to make K2 = 4000, K2 is a SUM

[#2]

Hi @jennigetz

Try =IF(K2<4000, 4000-K2, 0)

Cheers

Damien

[#3]

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." :(

[#4]

Hi @jennigetz

Did you put the formula into L2? If you have put it into K2 it will become circular.

Cheers

Damien

[#5]

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!

[#6]

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

[#7]

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...

[#8]

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

[#9]

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. 

[#10]

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!

[#11]

This is what I was afraid of.

Thank you so much for your response

[#12]

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