| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula gives unwanted result
Hi Experts:
I've poured over the other questions and responses on this site and either missed my situation or it's not there...I need help with what should be so simple... Example: B13 is 20...I want C13 to give me the result of 3 of B13 is greater than 17....I then need the formula to say...If B13 is not greater than 17, enter 0. This formula that I've started returns the result of 1 not 3...I then need to expand the formula to enter zero if B13 is not greater than 17. The result would be returned in C13. =SUM($B1317) I know this is really simple, so be kind....my brain is fried.... Thank you so much for all your expertise..... -- jeannie v |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula gives unwanted result
Maybe this is what you want:
=IF(B1317,3,0) HTH, Paul -- "jeannie v" wrote in message ... Hi Experts: I've poured over the other questions and responses on this site and either missed my situation or it's not there...I need help with what should be so simple... Example: B13 is 20...I want C13 to give me the result of 3 of B13 is greater than 17....I then need the formula to say...If B13 is not greater than 17, enter 0. This formula that I've started returns the result of 1 not 3...I then need to expand the formula to enter zero if B13 is not greater than 17. The result would be returned in C13. =SUM($B1317) I know this is really simple, so be kind....my brain is fried.... Thank you so much for all your expertise..... -- jeannie v |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula gives unwanted result
On Wed, 5 Mar 2008 07:30:01 -0800, jeannie v
wrote: Hi Experts: I've poured over the other questions and responses on this site and either missed my situation or it's not there...I need help with what should be so simple... Example: B13 is 20...I want C13 to give me the result of 3 of B13 is greater than 17....I then need the formula to say...If B13 is not greater than 17, enter 0. This formula that I've started returns the result of 1 not 3...I then need to expand the formula to enter zero if B13 is not greater than 17. The result would be returned in C13. =SUM($B1317) I know this is really simple, so be kind....my brain is fried.... Thank you so much for all your expertise..... Look closely at your formula: B1317 is a Boolean expression that will return TRUE or FALSE depending. Converting it to a numeric value, as your are doing using the SUM function, converts TRUE to 1 and FALSE to 0. So something like: C13: =IF(B1320,"undefined",IF(B13=20,3,IF(B1317,"unde fined","ENTER 0"))) --ron |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula gives unwanted result
You didn't say what to do if B13 EQUALS 17.
This returns 3 if B13 is greater then 17 AND 0 if B13 is less then OR equal to 17: =(B1317)*3 -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "jeannie v" wrote in message ... Hi Experts: I've poured over the other questions and responses on this site and either missed my situation or it's not there...I need help with what should be so simple... Example: B13 is 20...I want C13 to give me the result of 3 of B13 is greater than 17....I then need the formula to say...If B13 is not greater than 17, enter 0. This formula that I've started returns the result of 1 not 3...I then need to expand the formula to enter zero if B13 is not greater than 17. The result would be returned in C13. =SUM($B1317) I know this is really simple, so be kind....my brain is fried.... Thank you so much for all your expertise..... -- jeannie v |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula gives unwanted result
Hi PCLIVE:
OMG...this wa so easy.....it works...I've wasted so much time trying to figure it out...I can't thank you enough for helping me!!!!! -- jeannie v "PCLIVE" wrote: Maybe this is what you want: =IF(B1317,3,0) HTH, Paul -- "jeannie v" wrote in message ... Hi Experts: I've poured over the other questions and responses on this site and either missed my situation or it's not there...I need help with what should be so simple... Example: B13 is 20...I want C13 to give me the result of 3 of B13 is greater than 17....I then need the formula to say...If B13 is not greater than 17, enter 0. This formula that I've started returns the result of 1 not 3...I then need to expand the formula to enter zero if B13 is not greater than 17. The result would be returned in C13. =SUM($B1317) I know this is really simple, so be kind....my brain is fried.... Thank you so much for all your expertise..... -- jeannie v |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula gives unwanted result
Hi Paul:
OK...now if the 3 is variable, how would I replace the 3 so that whatever the result is it will be returned? Thank you for all your help, -- jeannie v "PCLIVE" wrote: Maybe this is what you want: =IF(B1317,3,0) HTH, Paul -- "jeannie v" wrote in message ... Hi Experts: I've poured over the other questions and responses on this site and either missed my situation or it's not there...I need help with what should be so simple... Example: B13 is 20...I want C13 to give me the result of 3 of B13 is greater than 17....I then need the formula to say...If B13 is not greater than 17, enter 0. This formula that I've started returns the result of 1 not 3...I then need to expand the formula to enter zero if B13 is not greater than 17. The result would be returned in C13. =SUM($B1317) I know this is really simple, so be kind....my brain is fried.... Thank you so much for all your expertise..... -- jeannie v |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula gives unwanted result
Hi Ron:
Thank you for your answer and it so appreciated!!!! I've tried RagDyer's answer and it works perfectly.....I will use your formula for another application that I'm working on...so I can't thank you enough! -- jeannie v "Ron Rosenfeld" wrote: On Wed, 5 Mar 2008 07:30:01 -0800, jeannie v wrote: Hi Experts: I've poured over the other questions and responses on this site and either missed my situation or it's not there...I need help with what should be so simple... Example: B13 is 20...I want C13 to give me the result of 3 of B13 is greater than 17....I then need the formula to say...If B13 is not greater than 17, enter 0. This formula that I've started returns the result of 1 not 3...I then need to expand the formula to enter zero if B13 is not greater than 17. The result would be returned in C13. =SUM($B1317) I know this is really simple, so be kind....my brain is fried.... Thank you so much for all your expertise..... Look closely at your formula: B1317 is a Boolean expression that will return TRUE or FALSE depending. Converting it to a numeric value, as your are doing using the SUM function, converts TRUE to 1 and FALSE to 0. So something like: C13: =IF(B1320,"undefined",IF(B13=20,3,IF(B1317,"unde fined","ENTER 0"))) --ron |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula gives unwanted result
Assuming your 3 (variable) will be in A1:
=IF(B1317,A1,0) Adjust "A1" to the cell containing 3 or your variable. I'm not sure if that is what you were saying. If not, please elaborate. Regards, Paul -- "jeannie v" wrote in message ... Hi Paul: OK...now if the 3 is variable, how would I replace the 3 so that whatever the result is it will be returned? Thank you for all your help, -- jeannie v "PCLIVE" wrote: Maybe this is what you want: =IF(B1317,3,0) HTH, Paul -- "jeannie v" wrote in message ... Hi Experts: I've poured over the other questions and responses on this site and either missed my situation or it's not there...I need help with what should be so simple... Example: B13 is 20...I want C13 to give me the result of 3 of B13 is greater than 17....I then need the formula to say...If B13 is not greater than 17, enter 0. This formula that I've started returns the result of 1 not 3...I then need to expand the formula to enter zero if B13 is not greater than 17. The result would be returned in C13. =SUM($B1317) I know this is really simple, so be kind....my brain is fried.... Thank you so much for all your expertise..... -- jeannie v |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula gives unwanted result
HiRagDyeR:
Well...you have given me the formula that works for the entire worksheet and I am so appreciative. Thank you...you have relieved me of that stress you experience when you keep trying different methods over and over and just get so frustrated. I thank you for your expertise! -- jeannie v "RagDyeR" wrote: You didn't say what to do if B13 EQUALS 17. This returns 3 if B13 is greater then 17 AND 0 if B13 is less then OR equal to 17: =(B1317)*3 -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "jeannie v" wrote in message ... Hi Experts: I've poured over the other questions and responses on this site and either missed my situation or it's not there...I need help with what should be so simple... Example: B13 is 20...I want C13 to give me the result of 3 of B13 is greater than 17....I then need the formula to say...If B13 is not greater than 17, enter 0. This formula that I've started returns the result of 1 not 3...I then need to expand the formula to enter zero if B13 is not greater than 17. The result would be returned in C13. =SUM($B1317) I know this is really simple, so be kind....my brain is fried.... Thank you so much for all your expertise..... -- jeannie v |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula gives unwanted result
You can put your variable value in a particular cell, and have the formula
refer to that cell so that all you need to do is change the value in that cell. Say you use A1 for the value, try this: =(B1317)*A1 -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "jeannie v" wrote in message ... Hi Paul: OK...now if the 3 is variable, how would I replace the 3 so that whatever the result is it will be returned? Thank you for all your help, -- jeannie v "PCLIVE" wrote: Maybe this is what you want: =IF(B1317,3,0) HTH, Paul -- "jeannie v" wrote in message ... Hi Experts: I've poured over the other questions and responses on this site and either missed my situation or it's not there...I need help with what should be so simple... Example: B13 is 20...I want C13 to give me the result of 3 of B13 is greater than 17....I then need the formula to say...If B13 is not greater than 17, enter 0. This formula that I've started returns the result of 1 not 3...I then need to expand the formula to enter zero if B13 is not greater than 17. The result would be returned in C13. =SUM($B1317) I know this is really simple, so be kind....my brain is fried.... Thank you so much for all your expertise..... -- jeannie v |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula gives unwanted result
Hi Paul:
Thank you for all your help.....I used RagDyeR's formula and it works....I will also keep your's of file for use in the future... You have been so kind and helpful and I appreciate it! -- jeannie v "PCLIVE" wrote: Assuming your 3 (variable) will be in A1: =IF(B1317,A1,0) Adjust "A1" to the cell containing 3 or your variable. I'm not sure if that is what you were saying. If not, please elaborate. Regards, Paul -- "jeannie v" wrote in message ... Hi Paul: OK...now if the 3 is variable, how would I replace the 3 so that whatever the result is it will be returned? Thank you for all your help, -- jeannie v "PCLIVE" wrote: Maybe this is what you want: =IF(B1317,3,0) HTH, Paul -- "jeannie v" wrote in message ... Hi Experts: I've poured over the other questions and responses on this site and either missed my situation or it's not there...I need help with what should be so simple... Example: B13 is 20...I want C13 to give me the result of 3 of B13 is greater than 17....I then need the formula to say...If B13 is not greater than 17, enter 0. This formula that I've started returns the result of 1 not 3...I then need to expand the formula to enter zero if B13 is not greater than 17. The result would be returned in C13. =SUM($B1317) I know this is really simple, so be kind....my brain is fried.... Thank you so much for all your expertise..... -- jeannie v |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Unwanted formula being copied to cell | Excel Discussion (Misc queries) | |||
| Unwanted result | Excel Discussion (Misc queries) | |||
| Advanced formula - Return result & Show Cell Reference of result | Excel Worksheet Functions | |||
| Median result used in formula gives incorrect result | Excel Worksheet Functions | |||
| Unwanted LOOKUP() Result | Excel Discussion (Misc queries) | |||
Linear Mode
