Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I add iserr or iserror to this
Trying to get rid of the #value to the cells that are not currently populated
in cells with this if statement =IF(M29-(M29*Disc),M29-(M29*Disc),M29) Any help would be great Thanks -- Neall |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I add iserr or iserror to this
Presumably, either disc or M29 contains non-numeric (or non-boolean) data.
Does the following fix the problem? =IF(ISNUMBER(M29*disc),M29-(M29*disc),M29) -- Steve "Neall" wrote in message ... Trying to get rid of the #value to the cells that are not currently populated in cells with this if statement =IF(M29-(M29*Disc),M29-(M29*Disc),M29) Any help would be great Thanks -- Neall |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I add iserr or iserror to this
Thanks for the suggestion Steve, but it wont work,
I thought this might work =IF(ISERROR(M29-(M29*Disc),M29-(M29*Disc),M29),"",M29-(M29*Disc),M29-(M29*Disc),M29) However it keeps telling me that I have to many arguments, I need these arguments included for other variables involved. Any other suggestions? Thanks in advance -- Neall "Neall" wrote: Trying to get rid of the #value to the cells that are not currently populated in cells with this if statement =IF(M29-(M29*Disc),M29-(M29*Disc),M29) Any help would be great Thanks -- Neall |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I add iserr or iserror to this
What values are in M29 and disc?
-- Steve "Neall" wrote in message ... Thanks for the suggestion Steve, but it wont work, I thought this might work =IF(ISERROR(M29-(M29*Disc),M29-(M29*Disc),M29),"",M29-(M29*Disc),M29-(M29*Disc),M29) However it keeps telling me that I have to many arguments, I need these arguments included for other variables involved. Any other suggestions? Thanks in advance -- Neall "Neall" wrote: Trying to get rid of the #value to the cells that are not currently populated in cells with this if statement =IF(M29-(M29*Disc),M29-(M29*Disc),M29) Any help would be great Thanks -- Neall |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I add iserr or iserror to this
On 5/29/09 8:57 AM, Neall wrote:
I guess I should explain, I have a set of cells that show full price M29 - M36 then I have another set of cells that show price either A) as full price (pulling from M29 - M36) or the discounted price Q29 - Q36. So basically if there is no discount the full price of the parts get shown in Q29 - Q36 if there are discounts to be had then Q29-36 show the discounted amount for each part So in Q29 - Q36 I have this =IF(M36-(M36*Disc),M36-(M36*Disc),M36) Which is saying if there is no discount then take the numbers from M and display them in Q, if there is a discounted noted show the discounted price and not the values in M This works now I just need to get ride of the #Value! in those cells when there is no part to price against, because its effecting the sum Thanks in advance Neall, The first argument (logical_test) in an IF statement needs to evaluate to a boolean result. In your formula above, the evaluation of "M36-(M36*Disc)" is likely numeric which will lead to unpredictable results. You might try something even simpler like this: =IF(ISNUMBER(M36),M36-(M36*Disc),"") -- Regards, Art |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I add iserr or iserror to this
Thanks but that doesn't work, here is the syntax I thought would work, I use
it on my other formula's to get rid of "#Value" in my cells when the cell should be blank =IF(ISERR((M29-(M29*Disc),M29-(M29*Disc),M29)),"",(M29-(M29*Disc),M29-(M29*Disc),M29)) Again basically if there is no discount to be given the cell should just be populated with what is in M29, if there is a discount to be given (Disc would have a percentage) then is will do the math to show the discounted amount in the cell. Again this all works great but when there is no part number to price against or give a discount against I get the #Value error in the field because the answer is 0, which messes up price sum value. Any suggestions how I can get this to work so the cell is empty when there is no pricing involved? Thanks -- Neall "Art" wrote: On 5/29/09 8:57 AM, Neall wrote: I guess I should explain, I have a set of cells that show full price M29 - M36 then I have another set of cells that show price either A) as full price (pulling from M29 - M36) or the discounted price Q29 - Q36. So basically if there is no discount the full price of the parts get shown in Q29 - Q36 if there are discounts to be had then Q29-36 show the discounted amount for each part So in Q29 - Q36 I have this =IF(M36-(M36*Disc),M36-(M36*Disc),M36) Which is saying if there is no discount then take the numbers from M and display them in Q, if there is a discounted noted show the discounted price and not the values in M This works now I just need to get ride of the #Value! in those cells when there is no part to price against, because its effecting the sum Thanks in advance Neall, The first argument (logical_test) in an IF statement needs to evaluate to a boolean result. In your formula above, the evaluation of "M36-(M36*Disc)" is likely numeric which will lead to unpredictable results. You might try something even simpler like this: =IF(ISNUMBER(M36),M36-(M36*Disc),"") -- Regards, Art |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I add iserr or iserror to this
Neall,
The equation as shown in your 5/29/09 2:40PM posting has errors and can't be entered as shown. I tested the equation that I posted and would appear to cover all of your conditions. I assigned the name "Disc" to a cell formatted as percentage. M36 and Q36 tested as either General or Currency formats with same results. formula in Q36: =IF(ISNUMBER(M36),M36-(M36*Disc),"") Tested on Excel Office X, and 2004 Some examples: M36: Disc: Result in Q36 100 0% 100 100 5% 95 blank 0% blank 100 blank blank blank blank blank Is this what you intended ? Art On 5/29/09 2:40 PM, Neall wrote: Thanks but that doesn't work, here is the syntax I thought would work, I use it on my other formula's to get rid of "#Value" in my cells when the cell should be blank =IF(ISERR((M29-(M29*Disc),M29-(M29*Disc),M29)),"",(M29-(M29*Disc),M29-(M29*Disc),M29)) Again basically if there is no discount to be given the cell should just be populated with what is in M29, if there is a discount to be given (Disc would have a percentage) then is will do the math to show the discounted amount in the cell. Again this all works great but when there is no part number to price against or give a discount against I get the #Value error in the field because the answer is 0, which messes up price sum value. Any suggestions how I can get this to work so the cell is empty when there is no pricing involved? Thanks "Art" wrote: On 5/29/09 8:57 AM, Neall wrote: I guess I should explain, I have a set of cells that show full price M29 - M36 then I have another set of cells that show price either A) as full price (pulling from M29 - M36) or the discounted price Q29 - Q36. So basically if there is no discount the full price of the parts get shown in Q29 - Q36 if there are discounts to be had then Q29-36 show the discounted amount for each part So in Q29 - Q36 I have this =IF(M36-(M36*Disc),M36-(M36*Disc),M36) Which is saying if there is no discount then take the numbers from M and display them in Q, if there is a discounted noted show the discounted price and not the values in M This works now I just need to get ride of the #Value! in those cells when there is no part to price against, because its effecting the sum Thanks in advance Neall, The first argument (logical_test) in an IF statement needs to evaluate to a boolean result. In your formula above, the evaluation of "M36-(M36*Disc)" is likely numeric which will lead to unpredictable results. You might try something even simpler like this: =IF(ISNUMBER(M36),M36-(M36*Disc),"") -- Regards, Art |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I add iserr or iserror to this
Neall,
I am also having problems understanding why your formula does not work. Breaking it down to its simples form, your formula is Result = Price-(Price*Discount) I tested and found the following: If you have no data in Discount or Price, Result = Price If you blank out a value in Discount or Price using the space bar, you get #Value! error. If you enter any other non-numeric value in Discount or Price, you get #Value! error. Provided you have NUMERIC values or a genuine blank cell in Price and Discount, you will ALWAYS get an answer in Result. To test data: Somewhere else on your spreadsheet use the formula =ISNUMBER(yourCellToTest) If you get false, fix the relevant cell by selecting it and fixing a formula or clearing the content. Hmmmmmm, try this first: 1) In a blank cell, type 1 2) Copy that cell 3) Highlight all price and discount values in your sheet (not formula) 4) Paste Special 5) Choose 'Operation' 'Multiply' 6) Click 'OK' -- Steve "Art" wrote in message ... Neall, The equation as shown in your 5/29/09 2:40PM posting has errors and can't be entered as shown. I tested the equation that I posted and would appear to cover all of your conditions. I assigned the name "Disc" to a cell formatted as percentage. M36 and Q36 tested as either General or Currency formats with same results. formula in Q36: =IF(ISNUMBER(M36),M36-(M36*Disc),"") Tested on Excel Office X, and 2004 Some examples: M36: Disc: Result in Q36 100 0% 100 100 5% 95 blank 0% blank 100 blank blank blank blank blank Is this what you intended ? Art On 5/29/09 2:40 PM, Neall wrote: Thanks but that doesn't work, here is the syntax I thought would work, I use it on my other formula's to get rid of "#Value" in my cells when the cell should be blank =IF(ISERR((M29-(M29*Disc),M29-(M29*Disc),M29)),"",(M29-(M29*Disc),M29-(M29*Disc),M29)) Again basically if there is no discount to be given the cell should just be populated with what is in M29, if there is a discount to be given (Disc would have a percentage) then is will do the math to show the discounted amount in the cell. Again this all works great but when there is no part number to price against or give a discount against I get the #Value error in the field because the answer is 0, which messes up price sum value. Any suggestions how I can get this to work so the cell is empty when there is no pricing involved? Thanks "Art" wrote: On 5/29/09 8:57 AM, Neall wrote: I guess I should explain, I have a set of cells that show full price M29 - M36 then I have another set of cells that show price either A) as full price (pulling from M29 - M36) or the discounted price Q29 - Q36. So basically if there is no discount the full price of the parts get shown in Q29 - Q36 if there are discounts to be had then Q29-36 show the discounted amount for each part So in Q29 - Q36 I have this =IF(M36-(M36*Disc),M36-(M36*Disc),M36) Which is saying if there is no discount then take the numbers from M and display them in Q, if there is a discounted noted show the discounted price and not the values in M This works now I just need to get ride of the #Value! in those cells when there is no part to price against, because its effecting the sum Thanks in advance Neall, The first argument (logical_test) in an IF statement needs to evaluate to a boolean result. In your formula above, the evaluation of "M36-(M36*Disc)" is likely numeric which will lead to unpredictable results. You might try something even simpler like this: =IF(ISNUMBER(M36),M36-(M36*Disc),"") -- Regards, Art |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I add iserr or iserror to this
Thanks Art you were right, my math for disc was wrong and it was throwing
everything off. Thanks very much! -- Neall "Art" wrote: Neall, The equation as shown in your 5/29/09 2:40PM posting has errors and can't be entered as shown. I tested the equation that I posted and would appear to cover all of your conditions. I assigned the name "Disc" to a cell formatted as percentage. M36 and Q36 tested as either General or Currency formats with same results. formula in Q36: =IF(ISNUMBER(M36),M36-(M36*Disc),"") Tested on Excel Office X, and 2004 Some examples: M36: Disc: Result in Q36 100 0% 100 100 5% 95 blank 0% blank 100 blank blank blank blank blank Is this what you intended ? Art On 5/29/09 2:40 PM, Neall wrote: Thanks but that doesn't work, here is the syntax I thought would work, I use it on my other formula's to get rid of "#Value" in my cells when the cell should be blank =IF(ISERR((M29-(M29*Disc),M29-(M29*Disc),M29)),"",(M29-(M29*Disc),M29-(M29*Disc),M29)) Again basically if there is no discount to be given the cell should just be populated with what is in M29, if there is a discount to be given (Disc would have a percentage) then is will do the math to show the discounted amount in the cell. Again this all works great but when there is no part number to price against or give a discount against I get the #Value error in the field because the answer is 0, which messes up price sum value. Any suggestions how I can get this to work so the cell is empty when there is no pricing involved? Thanks "Art" wrote: On 5/29/09 8:57 AM, Neall wrote: I guess I should explain, I have a set of cells that show full price M29 - M36 then I have another set of cells that show price either A) as full price (pulling from M29 - M36) or the discounted price Q29 - Q36. So basically if there is no discount the full price of the parts get shown in Q29 - Q36 if there are discounts to be had then Q29-36 show the discounted amount for each part So in Q29 - Q36 I have this =IF(M36-(M36*Disc),M36-(M36*Disc),M36) Which is saying if there is no discount then take the numbers from M and display them in Q, if there is a discounted noted show the discounted price and not the values in M This works now I just need to get ride of the #Value! in those cells when there is no part to price against, because its effecting the sum Thanks in advance Neall, The first argument (logical_test) in an IF statement needs to evaluate to a boolean result. In your formula above, the evaluation of "M36-(M36*Disc)" is likely numeric which will lead to unpredictable results. You might try something even simpler like this: =IF(ISNUMBER(M36),M36-(M36*Disc),"") -- Regards, Art |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif IsErr | Excel Worksheet Functions | |||
ISERR | Excel Worksheet Functions | |||
Need for ISERR and ISNA Makes formula too Long | Excel Worksheet Functions | |||
What does this function do =+IF(ISERR(F27/G27),0,F27/G27) | Excel Worksheet Functions | |||
ISERR | Excel Worksheet Functions |