How do I treat a negative number as a positive in a formula?

[Thistledown]

I am trying to calculate the monthly interest payable on an overdraft, but it is returning '0' because the cell being looked up is a negative number. Eg:

cell A1 = -£250,000

formula

=(A1*0.0275)/12

it should be returning £572.92 but is returning 0.

[Debraj Roy]

try this..

Please Login or Register  to view this content.

[oeldere]

abs() wil make the cell absolut.

[Thistledown]

It didnt work - maybe because A1 is a formula itself?

[Debraj Roy]

A1 is a formula itself

will never affect on another formula..

Its working...

[oeldere]

The formula of Debra Roy should work for you in e.g. cell B1.

you try the put the formula in cell A1?

That can't be done with excel; probably it's possible with VBA.

[Thistledown]

Please see cell C26 in this attachment.

Sample.xlsx

[oeldere]

The problem in your file is you have circle references in your file.

The result of c26 =(ABS(C39)*0,0275)/12

depends on the results on c39.

c39 depends on the result of C33.

c33 =SOM(C9:C32)

You have to change the formula of cell c26 (remove the relationship with cell c39).

[Thistledown]

Yes I see now, but a problem how to change it....

[oeldere]

You made the file, so you will have to give us the answer.

But, if you found the answer yourself, i bet you can solve the problem also in the file.

[Rambo4711]

You can activate iteration calculation in your general settings

[Thistledown]

You can activate iteration calculation in your general settings

Brilliant, thank you - it has worked when I reduced the maximum iterations from default 100 to 10. Is this the correct thing to do, and what are the implications to this and all other workbooks?

[Rambo4711]

The only implication is that if your first opened workbook in the Excel session is set without iteration, the following workbooks opened will have the non iteration setting. If so, you have to activate the iteration calculation again.

[Thistledown]

Thank you.