EXCEL FORMULA WON'T COPY TO ANOTHER CELL

[binglelmh]

This must be very simple but I've never had it happen before. I have a fairly simple formula that I copy to new lines inserted in a spreadsheet that works perfectly for all the current rows, but when I just added a row, it won't work. I just tried to copy/ paste special/ formula only/ to a new row and the formula shows up in the cell but it won't calculate.

it is: =ROUND(J12*(1+L12),2)*H12
J12 is a value = 80 (cell format is TEXT)
L12 is a value = %, but on the row I'm talking about , it is blank (no %)(cell format is PERCENTAGE)
H12 is A VALUE = $29.00 (cell format is CURRENCY)

It is for payroll purposes and it is supposed to calculate a # of hours worked times a payrate . If there is a % of premium pay, it calcultes that in as well .

I have checked the formats of the cells and they match the formats of the cells in the rows that work ok.

What on earth is happening...I fell like I'm cursed.????

 

[AnotherHiggins]

>it won't calculate

What does it do? Is it giving you an error of some kind? Is it displaying the actual formula in the cell?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[dune2]

Is it possible your excel is not auto-updating the cells and you have to manually push F9 first to get a result?

Also, you wrote that "J12 is a value = 80 (cell format is TEXT)", so is it possible that for excel you try to round not a number but a text field, hence spitting out an error?

 

[AnotherHiggins]

dune2,

I read right over that "format is text" bit. Good catch.

But that wouldn't prevent the formula from working.

That's because before evaluating the ROUND function, the Text-formatted 80 is multiplied by (1+L12).

Multiplying a text-formatted number by any actual number will convert the text-formatted number to a - er - number.

Clear as mud? Let me try again:

[tt]Dog * 1[/tt] returns an error.
[tt]'02 * 1[/tt] returns 2.

(the ' before a number, when entered into a cell, will cause whatever follows to be considered text)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[binglelmh]

Thanks to everybody who responded. ANOTHERHIGGINS - after I paste the formula in, and enter, the cell reads $0.00. As if there are no values in the cells that the formula targets.

It gets wierder....I just copied the whole spreadsheet to a blank worksheet so I could try out the F9 function mentioned by DUNE2 which unfortunately didn't work. When I copied and pasted the formula into the row this time, IT WORKED!!! (I literally copied the whole darn spreadsheet to a blank worksheet, then copied the formula from one of the rows above the new problem row,<working strictly inside the new worksheet> pasted it into the problem row and the darn thing calculated the value perfectly)

Please don't give up on me...this is making me nuts.