Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
And, if, arguments....
I am trying to get a cell to do this:
Look at cell B7, if B7 has a vlaue of greater than or equal to 0, return a value of 4 to cell Z4, if cell B7 is empty return a value of 0 to cell Z4.... Can you help? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
And, if, arguments....
=IF(B7=0,4,IF(ISBLANK(B7),0))
Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Chuck_in_Mo" wrote: I am trying to get a cell to do this: Look at cell B7, if B7 has a vlaue of greater than or equal to 0, return a value of 4 to cell Z4, if cell B7 is empty return a value of 0 to cell Z4.... Can you help? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
And, if, arguments....
Put this formula in Z4:
=if(b7=0,4,"whatdoyouwanthere?") (Excel will treat an empty cell as 0.) Chuck_in_Mo wrote: I am trying to get a cell to do this: Look at cell B7, if B7 has a vlaue of greater than or equal to 0, return a value of 4 to cell Z4, if cell B7 is empty return a value of 0 to cell Z4.... Can you help? -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
And, if, arguments....
I entered
=IF(B10=0,4,IF(ISBLANK(B10),0)) and it is still giving me a value of 4 when the cell in blank..... "Dave F" wrote: =IF(B7=0,4,IF(ISBLANK(B7),0)) Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Chuck_in_Mo" wrote: I am trying to get a cell to do this: Look at cell B7, if B7 has a vlaue of greater than or equal to 0, return a value of 4 to cell Z4, if cell B7 is empty return a value of 0 to cell Z4.... Can you help? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
And, if, arguments....
I put a 0 between the quotes, and am still getting a value of 4 in the cell
as if excel is seeing a blank cell as a value = zero.... "Dave Peterson" wrote: Put this formula in Z4: =if(b7=0,4,"whatdoyouwanthere?") (Excel will treat an empty cell as 0.) Chuck_in_Mo wrote: I am trying to get a cell to do this: Look at cell B7, if B7 has a vlaue of greater than or equal to 0, return a value of 4 to cell Z4, if cell B7 is empty return a value of 0 to cell Z4.... Can you help? -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
And, if, arguments....
No quote between 0
=IF(B7=0,4,"") "Chuck_in_Mo" wrote: I am trying to get a cell to do this: Look at cell B7, if B7 has a vlaue of greater than or equal to 0, return a value of 4 to cell Z4, if cell B7 is empty return a value of 0 to cell Z4.... Can you help? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
And, if, arguments....
Ignore my first reply
Here is the correction formula =IF(B7="","",IF(B7=0,4)) "Chuck_in_Mo" wrote: I am trying to get a cell to do this: Look at cell B7, if B7 has a vlaue of greater than or equal to 0, return a value of 4 to cell Z4, if cell B7 is empty return a value of 0 to cell Z4.... Can you help? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
And, if, arguments....
Hi Chuck,
Does B7 contains a formula that may give results from an error or "" or negative values ? Does B7 shall contain type-in values ? -- ***** birds of the same feather flock together.. "Chuck_in_Mo" wrote: I put a 0 between the quotes, and am still getting a value of 4 in the cell as if excel is seeing a blank cell as a value = zero.... "Dave Peterson" wrote: Put this formula in Z4: =if(b7=0,4,"whatdoyouwanthere?") (Excel will treat an empty cell as 0.) Chuck_in_Mo wrote: I am trying to get a cell to do this: Look at cell B7, if B7 has a vlaue of greater than or equal to 0, return a value of 4 to cell Z4, if cell B7 is empty return a value of 0 to cell Z4.... Can you help? -- Dave Peterson |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
And, if, arguments....
You probably need to put Dave's formula the other way round:
=IF(ISBLANK(B10),0,IF(B10=0,4,"undefined")) -- David Biddulph "Chuck_in_Mo" wrote in message ... I entered =IF(B10=0,4,IF(ISBLANK(B10),0)) and it is still giving me a value of 4 when the cell in blank..... "Dave F" wrote: =IF(B7=0,4,IF(ISBLANK(B7),0)) Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Chuck_in_Mo" wrote: I am trying to get a cell to do this: Look at cell B7, if B7 has a vlaue of greater than or equal to 0, return a value of 4 to cell Z4, if cell B7 is empty return a value of 0 to cell Z4.... Can you help? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
And, if, arguments....
And I misread your requirements...
=if(b7="",0,if(b7=0,4,"whatdoyouwanthere?")) Chuck_in_Mo wrote: I put a 0 between the quotes, and am still getting a value of 4 in the cell as if excel is seeing a blank cell as a value = zero.... "Dave Peterson" wrote: Put this formula in Z4: =if(b7=0,4,"whatdoyouwanthere?") (Excel will treat an empty cell as 0.) Chuck_in_Mo wrote: I am trying to get a cell to do this: Look at cell B7, if B7 has a vlaue of greater than or equal to 0, return a value of 4 to cell Z4, if cell B7 is empty return a value of 0 to cell Z4.... Can you help? -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
And, if, arguments....
Yes, you're right. Sorry about that. Brain's frozen!
Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "David Biddulph" wrote: You probably need to put Dave's formula the other way round: =IF(ISBLANK(B10),0,IF(B10=0,4,"undefined")) -- David Biddulph "Chuck_in_Mo" wrote in message ... I entered =IF(B10=0,4,IF(ISBLANK(B10),0)) and it is still giving me a value of 4 when the cell in blank..... "Dave F" wrote: =IF(B7=0,4,IF(ISBLANK(B7),0)) Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Chuck_in_Mo" wrote: I am trying to get a cell to do this: Look at cell B7, if B7 has a vlaue of greater than or equal to 0, return a value of 4 to cell Z4, if cell B7 is empty return a value of 0 to cell Z4.... Can you help? |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
And, if, arguments....
Thanks for your help Mama,
But, it doesn't work... still getting 4 for a blank cell.... "Teethless mama" wrote: No quote between 0 =IF(B7=0,4,"") "Chuck_in_Mo" wrote: I am trying to get a cell to do this: Look at cell B7, if B7 has a vlaue of greater than or equal to 0, return a value of 4 to cell Z4, if cell B7 is empty return a value of 0 to cell Z4.... Can you help? |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
And, if, arguments....
Thanks again... still getting the 4 value though....
"Teethless mama" wrote: Ignore my first reply Here is the correction formula =IF(B7="","",IF(B7=0,4)) "Chuck_in_Mo" wrote: I am trying to get a cell to do this: Look at cell B7, if B7 has a vlaue of greater than or equal to 0, return a value of 4 to cell Z4, if cell B7 is empty return a value of 0 to cell Z4.... Can you help? |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
And, if, arguments....
Dave,
I put a zero between the quotes, to reflect the balnk field... Still getting a 4..... "Dave Peterson" wrote: And I misread your requirements... =if(b7="",0,if(b7=0,4,"whatdoyouwanthere?")) Chuck_in_Mo wrote: I put a 0 between the quotes, and am still getting a value of 4 in the cell as if excel is seeing a blank cell as a value = zero.... "Dave Peterson" wrote: Put this formula in Z4: =if(b7=0,4,"whatdoyouwanthere?") (Excel will treat an empty cell as 0.) Chuck_in_Mo wrote: I am trying to get a cell to do this: Look at cell B7, if B7 has a vlaue of greater than or equal to 0, return a value of 4 to cell Z4, if cell B7 is empty return a value of 0 to cell Z4.... Can you help? -- Dave Peterson -- Dave Peterson |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
And, if, arguments....
If I undertsna dyour question correctly, the B7 field will either be blank
(as in this case) or have a value between 0 and 4.... The formula I am looking for will "look" at that B7 cell and either return with a blank or zero (in the case of a blank B7 cell) or return with a value of 4 if a value of 0 - 4 is entered into B7.... Hope this wasn't too confusing... Could it be some type of formatting issue? "driller" wrote: Hi Chuck, Does B7 contains a formula that may give results from an error or "" or negative values ? Does B7 shall contain type-in values ? -- ***** birds of the same feather flock together.. "Chuck_in_Mo" wrote: I put a 0 between the quotes, and am still getting a value of 4 in the cell as if excel is seeing a blank cell as a value = zero.... "Dave Peterson" wrote: Put this formula in Z4: =if(b7=0,4,"whatdoyouwanthere?") (Excel will treat an empty cell as 0.) Chuck_in_Mo wrote: I am trying to get a cell to do this: Look at cell B7, if B7 has a vlaue of greater than or equal to 0, return a value of 4 to cell Z4, if cell B7 is empty return a value of 0 to cell Z4.... Can you help? -- Dave Peterson |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
And, if, arguments....
Share your formula.
And what's in B7. Maybe... =if(trim(b7)="",0,if(b7=0,4,0)) (Don't use "0" if you want a real number--not text.) Chuck_in_Mo wrote: Dave, I put a zero between the quotes, to reflect the balnk field... Still getting a 4..... "Dave Peterson" wrote: And I misread your requirements... =if(b7="",0,if(b7=0,4,"whatdoyouwanthere?")) Chuck_in_Mo wrote: I put a 0 between the quotes, and am still getting a value of 4 in the cell as if excel is seeing a blank cell as a value = zero.... "Dave Peterson" wrote: Put this formula in Z4: =if(b7=0,4,"whatdoyouwanthere?") (Excel will treat an empty cell as 0.) Chuck_in_Mo wrote: I am trying to get a cell to do this: Look at cell B7, if B7 has a vlaue of greater than or equal to 0, return a value of 4 to cell Z4, if cell B7 is empty return a value of 0 to cell Z4.... Can you help? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
And, if, arguments....
Hi Mo,
from the original post.. if B7 has a vlaue of greater than or equal to 0, return a value of 4 to cell Z4, this means IF B7=0 THEN 4 if cell B7 is empty return a value of 0 to cell Z4... this means IF B7="" THEN 0 =if(b7="",0,4) -----this may be the case then you mentioned a while ago and either return with a blank or zero (in the case of a blank B7 cell) this means IF B7="" THEN RETURN "BLANK" OR 0 ! or return with a value of 4 if a value of 0 - 4 is entered into B7.... " this means IF AND(B7=0,B7<=4) THEN 4 =IF (B7="","",IF(AND(B7=0,B7<=4),4,"don't exceed 4")) Are these something you can work with? regards regards -- ***** birds of the same feather flock together.. "Chuck_in_Mo" wrote: If I undertsna dyour question correctly, the B7 field will either be blank (as in this case) or have a value between 0 and 4.... The formula I am looking for will "look" at that B7 cell and either return with a blank or zero (in the case of a blank B7 cell) or return with a value of 4 if a value of 0 - 4 is entered into B7.... Hope this wasn't too confusing... Could it be some type of formatting issue? "driller" wrote: Hi Chuck, Does B7 contains a formula that may give results from an error or "" or negative values ? Does B7 shall contain type-in values ? -- ***** birds of the same feather flock together.. "Chuck_in_Mo" wrote: I put a 0 between the quotes, and am still getting a value of 4 in the cell as if excel is seeing a blank cell as a value = zero.... "Dave Peterson" wrote: Put this formula in Z4: =if(b7=0,4,"whatdoyouwanthere?") (Excel will treat an empty cell as 0.) Chuck_in_Mo wrote: I am trying to get a cell to do this: Look at cell B7, if B7 has a vlaue of greater than or equal to 0, return a value of 4 to cell Z4, if cell B7 is empty return a value of 0 to cell Z4.... Can you help? -- Dave Peterson |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
And, if, arguments....
In which case, if your cell looks blank but returns the 4, you've probably
got one or more spaces (or other invisible characters) in a text string in B7. What does =LEN(B7) give you? If it doesn't return 0, go into B7 and delete the spaces or whatever is there. -- David Biddulph "Chuck_in_Mo" wrote in message ... Thanks again... still getting the 4 value though.... "Teethless mama" wrote: Ignore my first reply Here is the correction formula =IF(B7="","",IF(B7=0,4)) "Chuck_in_Mo" wrote: I am trying to get a cell to do this: Look at cell B7, if B7 has a vlaue of greater than or equal to 0, return a value of 4 to cell Z4, if cell B7 is empty return a value of 0 to cell Z4.... Can you help? |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
And, if, arguments....
As far as I can tell, Excel treats a blank cell as zero(0) so =0 will
return 4. Try it with 0 and see what occurs. Gord Dibben MS Excel MVP On Fri, 9 Feb 2007 21:19:31 -0000, "David Biddulph" wrote: In which case, if your cell looks blank but returns the 4, you've probably got one or more spaces (or other invisible characters) in a text string in B7. What does =LEN(B7) give you? If it doesn't return 0, go into B7 and delete the spaces or whatever is there. |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
And, if, arguments....
Thanks!
Thats it..... The formula wasn't working unless I clicked in the blank cell and hit the delete key, so, although it looked empty, it had something in it! "David Biddulph" wrote: In which case, if your cell looks blank but returns the 4, you've probably got one or more spaces (or other invisible characters) in a text string in B7. What does =LEN(B7) give you? If it doesn't return 0, go into B7 and delete the spaces or whatever is there. -- David Biddulph "Chuck_in_Mo" wrote in message ... Thanks again... still getting the 4 value though.... "Teethless mama" wrote: Ignore my first reply Here is the correction formula =IF(B7="","",IF(B7=0,4)) "Chuck_in_Mo" wrote: I am trying to get a cell to do this: Look at cell B7, if B7 has a vlaue of greater than or equal to 0, return a value of 4 to cell Z4, if cell B7 is empty return a value of 0 to cell Z4.... Can you help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Errors on Functions with many arguments | Excel Worksheet Functions | |||
Formulae containing more than 7 arguments? | Excel Worksheet Functions | |||
Arguments box on form | Excel Discussion (Misc queries) | |||
Passing Variable Number of Arguments to a Sub | Excel Discussion (Misc queries) | |||
Excel formulas increase from 7 arguments to 12 or more | Excel Discussion (Misc queries) |