Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing formulas that won't "formulate"
I keep coming on something thats bugging the heck out of me! I've searched
the database first to see if it's already been answered and I can't find it. Sometimes when I change a formula (usually adding something new to it) the formula won't work. Instead of the 'answer" or even an error in the cell, I see the actual formula (including the equal sign) Here's what I'm working on this morning. I have a formula that worked: =COUNTIF(D19:AH19,"R")+COUNTIF(D19:AH19,"") And I added one more condition to it: =COUNTIF(D19:AH19,"R")+COUNTIF(D19:AH19,"")+COUNTI F(D18:AH18,"") Now in the cell, I have the actual formula. (it should say 27) Why does this happen? it's not just on THIS formula. Thanks for all your help. I go through this forum often and I've learned so much from your answers to others -- I've even made an "Excel cheat sheet" with functions and formulas to save valuable info for later. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing formulas that won't "formulate"
As a guess the cell your formula is in has been formatted as text. Give this
a try... Write a formula in a cell (something simple like =A2+B2) Now format the cell that the formla is in to Text. Edit the formula in any way and hit enter Change the cells format to General Edit the formula again -- HTH... Jim Thomlinson "GIdunno" wrote: I keep coming on something thats bugging the heck out of me! I've searched the database first to see if it's already been answered and I can't find it. Sometimes when I change a formula (usually adding something new to it) the formula won't work. Instead of the 'answer" or even an error in the cell, I see the actual formula (including the equal sign) Here's what I'm working on this morning. I have a formula that worked: =COUNTIF(D19:AH19,"R")+COUNTIF(D19:AH19,"") And I added one more condition to it: =COUNTIF(D19:AH19,"R")+COUNTIF(D19:AH19,"")+COUNTI F(D18:AH18,"") Now in the cell, I have the actual formula. (it should say 27) Why does this happen? it's not just on THIS formula. Thanks for all your help. I go through this forum often and I've learned so much from your answers to others -- I've even made an "Excel cheat sheet" with functions and formulas to save valuable info for later. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing formulas that won't "formulate"
By re-enter I meant, press F2 followed by the Enter key
"GIdunno" wrote: I keep coming on something thats bugging the heck out of me! I've searched the database first to see if it's already been answered and I can't find it. Sometimes when I change a formula (usually adding something new to it) the formula won't work. Instead of the 'answer" or even an error in the cell, I see the actual formula (including the equal sign) Here's what I'm working on this morning. I have a formula that worked: =COUNTIF(D19:AH19,"R")+COUNTIF(D19:AH19,"") And I added one more condition to it: =COUNTIF(D19:AH19,"R")+COUNTIF(D19:AH19,"")+COUNTI F(D18:AH18,"") Now in the cell, I have the actual formula. (it should say 27) Why does this happen? it's not just on THIS formula. Thanks for all your help. I go through this forum often and I've learned so much from your answers to others -- I've even made an "Excel cheat sheet" with functions and formulas to save valuable info for later. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing formulas that won't "formulate"
Your cell is formatted as text. Select the offending cell(s), press Ctrl-1,
go to the Number tab and select an appropriate format. After than, you'll have to re-enter your formulas. "GIdunno" wrote: I keep coming on something thats bugging the heck out of me! I've searched the database first to see if it's already been answered and I can't find it. Sometimes when I change a formula (usually adding something new to it) the formula won't work. Instead of the 'answer" or even an error in the cell, I see the actual formula (including the equal sign) Here's what I'm working on this morning. I have a formula that worked: =COUNTIF(D19:AH19,"R")+COUNTIF(D19:AH19,"") And I added one more condition to it: =COUNTIF(D19:AH19,"R")+COUNTIF(D19:AH19,"")+COUNTI F(D18:AH18,"") Now in the cell, I have the actual formula. (it should say 27) Why does this happen? it's not just on THIS formula. Thanks for all your help. I go through this forum often and I've learned so much from your answers to others -- I've even made an "Excel cheat sheet" with functions and formulas to save valuable info for later. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing formulas that won't "formulate"
Are you looking at formulas?
Tools|Options|View tab|is Formulas checked? (xl2003 menu system) ctrl-` (ctrl-backquote to the left of the 1/! on my USA keyboard) will toggle this setting. If that's not it, select the range of offending cells Format|Cells|Number Tab|general (or anything but text) then edit|replace what: = (equal sign) with: = replace all Saved from a previous post. Excel likes to help. Try this on a test worksheet. Select A1 and hit ctrl-; (to put the date in the cell) now select B1 and type: =a1 Notice that excel changed the format of B1 to match the format in A1. Now format D1 as Text. put ASDF in D1 put =D1 in E1 You see ASDF. With E1 selected, hit the F2 key and then enter (to pretend that you're changing the formula). Excel has "helped" you by changing that cell's format to text. I don't know of any way of changing this behavior. I just select the cell, and reformat it to General (or whatever I wanted). I hit F2 and then enter (to reenter that formula). Sometimes this feature is nice, sometimes it ain't. GIdunno wrote: I keep coming on something thats bugging the heck out of me! I've searched the database first to see if it's already been answered and I can't find it. Sometimes when I change a formula (usually adding something new to it) the formula won't work. Instead of the 'answer" or even an error in the cell, I see the actual formula (including the equal sign) Here's what I'm working on this morning. I have a formula that worked: =COUNTIF(D19:AH19,"R")+COUNTIF(D19:AH19,"") And I added one more condition to it: =COUNTIF(D19:AH19,"R")+COUNTIF(D19:AH19,"")+COUNTI F(D18:AH18,"") Now in the cell, I have the actual formula. (it should say 27) Why does this happen? it's not just on THIS formula. Thanks for all your help. I go through this forum often and I've learned so much from your answers to others -- I've even made an "Excel cheat sheet" with functions and formulas to save valuable info for later. -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing formulas that won't "formulate"
Thank you both very much! Yes, the cell was fomated as Text. Sometimes it's
formatted as "Special" and I've had to go in and change it to "General" just to get my numbers to show up right. It never occurred to me that "Text" was the problem. **note to self; check the Format before entering functions** |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing formulas that won't "formulate"
Thanks Dave. No, Formula wasn't checked (I checked it to see what happens
and WOW! I went back and unchecked it LOL) You guys are awesome! "Dave Peterson" wrote: Are you looking at formulas? Tools|Options|View tab|is Formulas checked? (xl2003 menu system) ctrl-` (ctrl-backquote to the left of the 1/! on my USA keyboard) will toggle this setting. If that's not it, select the range of offending cells Format|Cells|Number Tab|general (or anything but text) then edit|replace what: = (equal sign) with: = replace all Saved from a previous post. Excel likes to help. Try this on a test worksheet. Select A1 and hit ctrl-; (to put the date in the cell) now select B1 and type: =a1 Notice that excel changed the format of B1 to match the format in A1. Now format D1 as Text. put ASDF in D1 put =D1 in E1 You see ASDF. With E1 selected, hit the F2 key and then enter (to pretend that you're changing the formula). Excel has "helped" you by changing that cell's format to text. I don't know of any way of changing this behavior. I just select the cell, and reformat it to General (or whatever I wanted). I hit F2 and then enter (to reenter that formula). Sometimes this feature is nice, sometimes it ain't. GIdunno wrote: I keep coming on something thats bugging the heck out of me! I've searched the database first to see if it's already been answered and I can't find it. Sometimes when I change a formula (usually adding something new to it) the formula won't work. Instead of the 'answer" or even an error in the cell, I see the actual formula (including the equal sign) Here's what I'm working on this morning. I have a formula that worked: =COUNTIF(D19:AH19,"R")+COUNTIF(D19:AH19,"") And I added one more condition to it: =COUNTIF(D19:AH19,"R")+COUNTIF(D19:AH19,"")+COUNTI F(D18:AH18,"") Now in the cell, I have the actual formula. (it should say 27) Why does this happen? it's not just on THIS formula. Thanks for all your help. I go through this forum often and I've learned so much from your answers to others -- I've even made an "Excel cheat sheet" with functions and formulas to save valuable info for later. -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing formulas that won't "formulate"
Me again :-)
You know what I noticed (now that you've opened my eyes)? The cell is formatted to General. I change the formula, and it reverts back to Text. THAT is what is causing my problems! I might not know how to make it STOP, but knowing this, I know how to fix it! You guys are awesome! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing formulas that won't "formulate"
That's excel "helping" again.
GIdunno wrote: Me again :-) You know what I noticed (now that you've opened my eyes)? The cell is formatted to General. I change the formula, and it reverts back to Text. THAT is what is causing my problems! I might not know how to make it STOP, but knowing this, I know how to fix it! You guys are awesome! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine logical formulas "if", "and", "or" | Excel Discussion (Misc queries) | |||
Combining formulas, "and" & "or" to verify content of multiple cel | Excel Discussion (Misc queries) | |||
Excel: Changing "numeric $" to "text $" in a different cell. | Excel Worksheet Functions | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Changing "returned" values from "0" to "blank" | Excel Worksheet Functions |