Function that gives huge numbers when correct solutions correspond to small numbers

**emaniftw** · 04-24-2011, 02:36 PM

Hello,
I am fairly new to excel so maybe my problem is easy to solve but I searched for a solution and I couldn't find it, so please help.

I got a table (3 columns and 7 rows) where I have the following function to calculate the values that I need, 1 per row.

"=IF(C6=0,0,(E6-$B$15)/C6)"

the B15 cell corresponds to "=-SUM(D6:D12)"

The problem is, when I insert a value in the "C" column excel exponentially increases the value for the function I mentioned earlier... this means that I got huge numbers when in fact small ones would be the correct ones.

I enabled limited iterations because there are circular functions.

You can find the table attached with some values entered so you can see the large numbers I get. I had macros in the original sheet which I removed because they don't matter for this problem,so if something appear just keep macros disabled.

Don't know if I made myself clear but please let me know if you didn't understand.

Would be very thankful if someone can help me!

**Cutter** · 04-24-2011, 05:00 PM

You have a circular reference.

B15 is: =-SUM(D6:D12)

every cell in D6:D12 refers to B15

Your Excel options were set to allow maximum 30 iterations.

**emaniftw** · 04-24-2011, 05:02 PM

yes, they are set for 30 iterations, would you change that?

**martindwilson** · 04-24-2011, 05:06 PM

i'd assume ( E6-$B$15)/6 is iterated 30 times since you have -SUM(D6:D12)
you get (e6+sum(d6:d12))/c6 that's probably why its growing

**Cutter** · 04-24-2011, 05:07 PM

I have that feature disabled.
I don't use circular references.

**martindwilson** · 04-24-2011, 05:12 PM

what exactly are you trying to calculate?

**emaniftw** · 04-24-2011, 05:13 PM

@martindwilson

even if I set it to 2-3 iteration the numbers are huge! have you checked the sheet I uploaded?

@Cutter

is there a way to make the same calculations without using a circular reference??

Thanyou both

**martindwilson** · 04-24-2011, 05:16 PM

if you allow iterations look at it this way
in your sheet in a1 put =a1+1
you will get the result of 30 ie it's calculated 30 times. as i asked before what exactly are you trying you calculate

**emaniftw** · 04-24-2011, 05:20 PM

let me give you an example.

imagine that you have different odds (corresponding to column A)
you have a stake that you want to calculate (column B) so that the balance (column C) is the same for all the rows. But the balance depends on the stakes, so I have to calculate the sum of the stakes so the value X (balance) can be equal for all rows.

you can see the attachment and try some numbers. Its easier to understand.

Thank you.

**Cutter** · 04-24-2011, 05:29 PM

I have no idea what you are trying to accomplish.

Maybe provide examples of values that should be showing and try to explain why those values are supposed to be reached.

Just one more note on the iterations - the formulas will be calculated 30 times every time the sheet is calculated. Hit the F9 key to see it happen.

**martindwilson** · 04-24-2011, 05:32 PM

i think you'd have to use solver

**emaniftw** · 04-24-2011, 05:43 PM

for example

A B C
1 13.5 23.7 266.80
2 16.5 19.4 267.00
3 32.0 10.0 266.90

being the A= odds, B=stakes, C=balance

note that C= A*B-SUM(B1:B3)

if I put these values in the table I made in excel it wont return these values..

**emaniftw** · 04-24-2011, 05:45 PM

I never used that! I will try it! Thanks

Function that gives huge numbers when correct solutions correspond to small numbers

LinkBack

Thread Tools

Rate This Thread

Display

Function that gives huge numbers when correct solutions correspond to small numbers

Re: Function that gives huge numbers when correct solutions correspond to small numbe

Re: Function that gives huge numbers when correct solutions correspond to small numbe

Re: Function that gives huge numbers when correct solutions correspond to small numbe

Re: Function that gives huge numbers when correct solutions correspond to small numbe

Re: Function that gives huge numbers when correct solutions correspond to small numbe

Re: Function that gives huge numbers when correct solutions correspond to small numbe

Re: Function that gives huge numbers when correct solutions correspond to small numbe

Re: Function that gives huge numbers when correct solutions correspond to small numbe

Re: Function that gives huge numbers when correct solutions correspond to small numbe

Re: Function that gives huge numbers when correct solutions correspond to small numbe

Re: Function that gives huge numbers when correct solutions correspond to small numbe

Re: Function that gives huge numbers when correct solutions correspond to small numbe

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions