Excel returning zeros (it shouldn't be)

Hi all,

Working on a model that was built by someone else and I am trying to make some changes.

When I first went to change a row of cells I couldn't even add in parenthesis without excel returning 0. Odd. I know my syntax is correct. So I assumed there was some strange number formatting occurring so I changed it to basic accounting style. But after dragging my new formula only about half the cells in my row are returning the correct values, while half are returning 0.

I'll list what I consider to be possible wrinkles:

-The formula is attempting to convert to a negative number...formula reads =-BS!L146*Interest_Rate/12

-I am now calling on a named cell opposed to a hardcoded number (although problem persists with a hardcoded number)


A final note is that the cells returning the correct numbers fail and return 0 if I add in parenthesis that shouldn't be changing the formula at all.

Thanks! I am stumped.

UPDATE: The cells that were working stop working if I simply click in the formula bar and then click out without changing anything. After that they return 0 again.

Prob best post some example (and non sensitive) data

I copied the text from Glenn Kennedy (thanks for that, Glenn)

Hi there. Try this aray formula. They're a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

oeldere... You may be losing the plot... Have you posted in the correct thread (Post No 3)...

Nope. Clear as mud!!

Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Glenn,

Reading this part, I decided I should post it.

Please Login or Register  to view this content.

You have created a circuar reference in BS F144 that is causing the problem. The offending formula:

=(F66-E66)+(PPMT(Interest_Rate/12,COUNTA(E144:F144),60,E144))

contains a reference to itself. I don't know what it should be... hopefully you do!!!!

Glad to have helped! Also, glad it worked - as I didn't want to have to learn about PPMTs....

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.