| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas using two cells
I have a formula in column G =MAX(MIN(220-F2)) that I want to set only if
the text in column E is set to the value of "H". If the value in column E is < H then I want the formula in column G to be =MAX(MIN(210-E2,60),0). How do I have this conditional formula in the same cell? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formulas using two cells
On Sat, 8 Aug 2009 07:14:01 -0700, Mark
wrote: I have a formula in column G =MAX(MIN(220-F2)) that I want to set only if the text in column E is set to the value of "H". If the value in column E is < H then I want the formula in column G to be =MAX(MIN(210-E2,60),0). How do I have this conditional formula in the same cell? Try this: =IF(G2="H", your first formula here , your second formula here ) Your first formula looks a bit odd. Have you mistyped it? If it really is =MAX(MIN(220-F2)) you can simplify it to just =220-F2 Hope this helps / Lars-Åke |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formulas using two cells
This is what I have now =IF(E3="H", 220-F3, MAX(MIN(210-E3,60),0) )
When E3 = H then the formula works fine, when it is not H then I get #Value! in the cell. "Lars-Ã…ke Aspelin" wrote: On Sat, 8 Aug 2009 07:14:01 -0700, Mark wrote: I have a formula in column G =MAX(MIN(220-F2)) that I want to set only if the text in column E is set to the value of "H". If the value in column E is < H then I want the formula in column G to be =MAX(MIN(210-E2,60),0). How do I have this conditional formula in the same cell? Try this: =IF(G2="H", your first formula here , your second formula here ) Your first formula looks a bit odd. Have you mistyped it? If it really is =MAX(MIN(220-F2)) you can simplify it to just =220-F2 Hope this helps / Lars-Ã…ke |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formulas using two cells
Your first formula doesn't make much sense as there is only a single argument meaning it is the same as 220-F2. Given that, try putting this in G2 (and copying down as needed)...
=IF(E2="H",220-F2,MAX(MIN(210-E2,60),0)) -- Rick (MVP - Excel) "Mark" wrote in message ... I have a formula in column G =MAX(MIN(220-F2)) that I want to set only if the text in column E is set to the value of "H". If the value in column E is < H then I want the formula in column G to be =MAX(MIN(210-E2,60),0). How do I have this conditional formula in the same cell? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formulas using two cells
When you have 210-E3; the formula expects a numeric in E3..It returns a
#value error because E3 is not a numeric.. If this post helps click Yes --------------- Jacob Skaria "Mark" wrote: This is what I have now =IF(E3="H", 220-F3, MAX(MIN(210-E3,60),0) ) When E3 = H then the formula works fine, when it is not H then I get #Value! in the cell. "Lars-Ã…ke Aspelin" wrote: On Sat, 8 Aug 2009 07:14:01 -0700, Mark wrote: I have a formula in column G =MAX(MIN(220-F2)) that I want to set only if the text in column E is set to the value of "H". If the value in column E is < H then I want the formula in column G to be =MAX(MIN(210-E2,60),0). How do I have this conditional formula in the same cell? Try this: =IF(G2="H", your first formula here , your second formula here ) Your first formula looks a bit odd. Have you mistyped it? If it really is =MAX(MIN(220-F2)) you can simplify it to just =220-F2 Hope this helps / Lars-Ã…ke |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formulas using two cells
And what do you have in E3 when the problem occurs?
If you dont have a number there, or something that can be interpreted as a number, Excel will have problems to calculate 210-E3. Lars-Åke On Sat, 8 Aug 2009 07:43:01 -0700, Mark wrote: This is what I have now =IF(E3="H", 220-F3, MAX(MIN(210-E3,60),0) ) When E3 = H then the formula works fine, when it is not H then I get #Value! in the cell. "Lars-Åke Aspelin" wrote: On Sat, 8 Aug 2009 07:14:01 -0700, Mark wrote: I have a formula in column G =MAX(MIN(220-F2)) that I want to set only if the text in column E is set to the value of "H". If the value in column E is < H then I want the formula in column G to be =MAX(MIN(210-E2,60),0). How do I have this conditional formula in the same cell? Try this: =IF(G2="H", your first formula here , your second formula here ) Your first formula looks a bit odd. Have you mistyped it? If it really is =MAX(MIN(220-F2)) you can simplify it to just =220-F2 Hope this helps / Lars-Åke |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formulas using two cells
That is because you MAX(MIN(210-E3,60),0)) expression is trying to do a mathematical operation on text (210 minus the contents of E3). Are you sure you want E3 to be subtracted from 210 or did you mean to write F3 (like you have in the first argument of the IF function?
-- Rick (MVP - Excel) "Mark" wrote in message ... This is what I have now =IF(E3="H", 220-F3, MAX(MIN(210-E3,60),0) ) When E3 = H then the formula works fine, when it is not H then I get #Value! in the cell. "Lars-Ã…ke Aspelin" wrote: On Sat, 8 Aug 2009 07:14:01 -0700, Mark wrote: I have a formula in column G =MAX(MIN(220-F2)) that I want to set only if the text in column E is set to the value of "H". If the value in column E is < H then I want the formula in column G to be =MAX(MIN(210-E2,60),0). How do I have this conditional formula in the same cell? Try this: =IF(G2="H", your first formula here , your second formula here ) Your first formula looks a bit odd. Have you mistyped it? If it really is =MAX(MIN(220-F2)) you can simplify it to just =220-F2 Hope this helps / Lars-Ã…ke |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formulas using two cells
That was it... it should have been 210-F3 and not E3... Thank you both for
the assistance. "Jacob Skaria" wrote: When you have 210-E3; the formula expects a numeric in E3..It returns a #value error because E3 is not a numeric.. If this post helps click Yes --------------- Jacob Skaria "Mark" wrote: This is what I have now =IF(E3="H", 220-F3, MAX(MIN(210-E3,60),0) ) When E3 = H then the formula works fine, when it is not H then I get #Value! in the cell. "Lars-Ã…ke Aspelin" wrote: On Sat, 8 Aug 2009 07:14:01 -0700, Mark wrote: I have a formula in column G =MAX(MIN(220-F2)) that I want to set only if the text in column E is set to the value of "H". If the value in column E is < H then I want the formula in column G to be =MAX(MIN(210-E2,60),0). How do I have this conditional formula in the same cell? Try this: =IF(G2="H", your first formula here , your second formula here ) Your first formula looks a bit odd. Have you mistyped it? If it really is =MAX(MIN(220-F2)) you can simplify it to just =220-F2 Hope this helps / Lars-Ã…ke |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formulas using two cells
=IF(E3="H", 220-F3, MAX(MIN(210-F3,60),0) )
Hi. Instead of MAX(MIN(210-F3,60),0) I like to use: =MEDIAN(0,210-F3,60) = = = = = = = HTH Dana DeLouis Mark wrote: That was it... it should have been 210-F3 and not E3... Thank you both for the assistance. "Jacob Skaria" wrote: When you have 210-E3; the formula expects a numeric in E3..It returns a #value error because E3 is not a numeric.. If this post helps click Yes --------------- Jacob Skaria "Mark" wrote: This is what I have now =IF(E3="H", 220-F3, MAX(MIN(210-E3,60),0) ) When E3 = H then the formula works fine, when it is not H then I get #Value! in the cell. "Lars-Ã…ke Aspelin" wrote: On Sat, 8 Aug 2009 07:14:01 -0700, Mark wrote: I have a formula in column G =MAX(MIN(220-F2)) that I want to set only if the text in column E is set to the value of "H". If the value in column E is < H then I want the formula in column G to be =MAX(MIN(210-E2,60),0). How do I have this conditional formula in the same cell? Try this: =IF(G2="H", your first formula here , your second formula here ) Your first formula looks a bit odd. Have you mistyped it? If it really is =MAX(MIN(220-F2)) you can simplify it to just =220-F2 Hope this helps / Lars-Ã…ke |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
| Formulas in Cells | Excel Discussion (Misc queries) | |||
| copy formulas from horizontal cells into vertical cells. | Excel Programming | |||
| Show cells with formulas without permanently changing the cells | Excel Programming | |||
| AdvancedFilter on cells with formulas, returning values and not formulas | Excel Programming | |||
Linear Mode
