Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Problem - interrupted by #VALUE! in other cells!?
Hi,
The below formula =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) is used to generate data. If there is only one data in the set, then it returns a #VALUE! Error message (as expected) The below formula is then used to show the absolute value of that answer in another set of cells =IF(N(CO23)=0,"",(ABS(CO23))) The problem being, I do need the absolute value of the data from those cells, but not the error code because it mucks/stops up other formula: =MATCH(MAX(B52:AD52),B52:AD52,0) The cell references may differ, but the formula is the same for all<< Does anyone know of a way to get the second formula to change #VALUE! for an empty cell, as the answer, when the #VALUE! error is generated, but transfer all numbers (where there are any) in tact!?? Thanks in advance, Ted. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Problem - interrupted by #VALUE! in other cells!?
Hi Ted
Perhaps =IF(OR(G23<G$45,G23G$46,G$47<1),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) Regards Roger Govier Ted wrote: Hi, The below formula =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) is used to generate data. If there is only one data in the set, then it returns a #VALUE! Error message (as expected) The below formula is then used to show the absolute value of that answer in another set of cells =IF(N(CO23)=0,"",(ABS(CO23))) The problem being, I do need the absolute value of the data from those cells, but not the error code because it mucks/stops up other formula: =MATCH(MAX(B52:AD52),B52:AD52,0) The cell references may differ, but the formula is the same for all<< Does anyone know of a way to get the second formula to change #VALUE! for an empty cell, as the answer, when the #VALUE! error is generated, but transfer all numbers (where there are any) in tact!?? Thanks in advance, Ted. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Problem - interrupted by #VALUE! in other cells!?
Hi, no sorry, that doesnt work - I think it needs to be inc in the
=IF(N(H27)=0,"",ROUND(H27*G$48,3)) formula as well, so that it is not affected by other formula, other than the absoult value one - any suggestions?? Thanks again, Ted. "Roger Govier" wrote: Hi Ted Perhaps =IF(OR(G23<G$45,G23G$46,G$47<1),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) Regards Roger Govier Ted wrote: Hi, The below formula =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) is used to generate data. If there is only one data in the set, then it returns a #VALUE! Error message (as expected) The below formula is then used to show the absolute value of that answer in another set of cells =IF(N(CO23)=0,"",(ABS(CO23))) The problem being, I do need the absolute value of the data from those cells, but not the error code because it mucks/stops up other formula: =MATCH(MAX(B52:AD52),B52:AD52,0) The cell references may differ, but the formula is the same for all<< Does anyone know of a way to get the second formula to change #VALUE! for an empty cell, as the answer, when the #VALUE! error is generated, but transfer all numbers (where there are any) in tact!?? Thanks in advance, Ted. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Problem - interrupted by #VALUE! in other cells!?
Hi Ted
I don't get any error with this formula. With 1 in H27 and Null in G48 from another formula, it returns 0. Send me a copy of your sheet directly and I will take a look for you. Remove NOSPAM from my address to send direct. Regards Roger Govier Ted wrote: Hi, no sorry, that doesnt work - I think it needs to be inc in the =IF(N(H27)=0,"",ROUND(H27*G$48,3)) formula as well, so that it is not affected by other formula, other than the absoult value one - any suggestions?? Thanks again, Ted. "Roger Govier" wrote: Hi Ted Perhaps =IF(OR(G23<G$45,G23G$46,G$47<1),"",IF(G$480,(G 23-G$45)/G$47,(G$46-G23)/G$47)) Regards Roger Govier Ted wrote: Hi, The below formula =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) is used to generate data. If there is only one data in the set, then it returns a #VALUE! Error message (as expected) The below formula is then used to show the absolute value of that answer in another set of cells =IF(N(CO23)=0,"",(ABS(CO23))) The problem being, I do need the absolute value of the data from those cells, but not the error code because it mucks/stops up other formula: =MATCH(MAX(B52:AD52),B52:AD52,0) The cell references may differ, but the formula is the same for all<< Does anyone know of a way to get the second formula to change #VALUE! for an empty cell, as the answer, when the #VALUE! error is generated, but transfer all numbers (where there are any) in tact!?? Thanks in advance, Ted. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Problem - interrupted by #VALUE! in other cells!?
Hi Roger, sorry for the delay - yeah, its not the formula that you sent that
causes a problem or anything, its that the one formula (the original/initial formula that these others get the data from) can generate the #VALUE/0! error because it is dividing the answer of previous sums. If there is no data in one of those cells, then it hits a problem because it cant divide 0 by 0 etc; so gives the #VALUE/0! error message. Then, when when I ask for the ABS value to be displayed in a final set of cells, it carries the #VALUE/0! error with it (because its the content of its dependant cell). So, what I was hoping to do, is add something to the last or one from last formula to 'weed-out'/remove the error text/values, and replace them with nothing. This means that I need a formula that basically says: original formula " =ABS(DM23) " Plus 'IF answer = "#VALUE/0!" THEN give answer "" ' {where "" means blank space/empty cell etc} Any idea how I can do tis please??? Ted. "Roger Govier" wrote: Hi Ted I don't get any error with this formula. With 1 in H27 and Null in G48 from another formula, it returns 0. Send me a copy of your sheet directly and I will take a look for you. Remove NOSPAM from my address to send direct. Regards Roger Govier Ted wrote: Hi, no sorry, that doesnt work - I think it needs to be inc in the =IF(N(H27)=0,"",ROUND(H27*G$48,3)) formula as well, so that it is not affected by other formula, other than the absoult value one - any suggestions?? Thanks again, Ted. "Roger Govier" wrote: Hi Ted Perhaps =IF(OR(G23<G$45,G23G$46,G$47<1),"",IF(G$480,(G 23-G$45)/G$47,(G$46-G23)/G$47)) Regards Roger Govier Ted wrote: Hi, The below formula =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) is used to generate data. If there is only one data in the set, then it returns a #VALUE! Error message (as expected) The below formula is then used to show the absolute value of that answer in another set of cells =IF(N(CO23)=0,"",(ABS(CO23))) The problem being, I do need the absolute value of the data from those cells, but not the error code because it mucks/stops up other formula: =MATCH(MAX(B52:AD52),B52:AD52,0) The cell references may differ, but the formula is the same for all<< Does anyone know of a way to get the second formula to change #VALUE! for an empty cell, as the answer, when the #VALUE! error is generated, but transfer all numbers (where there are any) in tact!?? Thanks in advance, Ted. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Problem - interrupted by #VALUE! in other cells!?
Hi Ted
As I said, send me a copy. It is easier than trying to describe where all the potential pitfalls lie. Regards Roger Govier Ted wrote: Hi Roger, sorry for the delay - yeah, its not the formula that you sent that causes a problem or anything, its that the one formula (the original/initial formula that these others get the data from) can generate the #VALUE/0! error because it is dividing the answer of previous sums. If there is no data in one of those cells, then it hits a problem because it cant divide 0 by 0 etc; so gives the #VALUE/0! error message. Then, when when I ask for the ABS value to be displayed in a final set of cells, it carries the #VALUE/0! error with it (because its the content of its dependant cell). So, what I was hoping to do, is add something to the last or one from last formula to 'weed-out'/remove the error text/values, and replace them with nothing. This means that I need a formula that basically says: original formula " =ABS(DM23) " Plus 'IF answer = "#VALUE/0!" THEN give answer "" ' {where "" means blank space/empty cell etc} Any idea how I can do tis please??? Ted. "Roger Govier" wrote: Hi Ted I don't get any error with this formula. With 1 in H27 and Null in G48 from another formula, it returns 0. Send me a copy of your sheet directly and I will take a look for you. Remove NOSPAM from my address to send direct. Regards Roger Govier Ted wrote: Hi, no sorry, that doesnt work - I think it needs to be inc in the =IF(N(H27)=0,"",ROUND(H27*G$48,3)) formula as well, so that it is not affected by other formula, other than the absoult value one - any suggestions?? Thanks again, Ted. "Roger Govier" wrote: Hi Ted Perhaps =IF(OR(G23<G$45,G23G$46,G$47<1),"",IF(G$480, (G23-G$45)/G$47,(G$46-G23)/G$47)) Regards Roger Govier Ted wrote: Hi, The below formula =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) is used to generate data. If there is only one data in the set, then it returns a #VALUE! Error message (as expected) The below formula is then used to show the absolute value of that answer in another set of cells =IF(N(CO23)=0,"",(ABS(CO23))) The problem being, I do need the absolute value of the data from those cells, but not the error code because it mucks/stops up other formula: =MATCH(MAX(B52:AD52),B52:AD52,0) The cell references may differ, but the formula is the same for all<< Does anyone know of a way to get the second formula to change #VALUE! for an empty cell, as the answer, when the #VALUE! error is generated, but transfer all numbers (where there are any) in tact!?? Thanks in advance, Ted. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Problem - interrupted by #VALUE! in other cells!?
Hi Roger, thanks for getting back to me. Unfortunately I am very restricted
in what I can send to you, as the database is part of a larger project, being conducted in a social science department of a University; where unfortunate, and very ridged guidelines apply. To make things more difficult still, the database contains actual subject data and information for me to delete this out, would then limit what can be seen, in terms of what has to be done to make the database perform as intended etc. Thank you for the offer though, it is appreciated. I apologise for the unusual situation, and hope there is a way you can still offer advice. With the term all I need being used very loosely; all I need really speaking is a formula that tells excel to show the absolute value of a cell (e.g. content of A1 displayed in A20). I need it to change any values that are anything but a number to a blank or a zero (blank is preferable); and need the formula to be encompassed in this formula: =ABS(A1) Thanks Roger, and sorry again for not being able to make things easier. Kind regards and speak soon, Ted. "Roger Govier" wrote: Hi Ted As I said, send me a copy. It is easier than trying to describe where all the potential pitfalls lie. Regards Roger Govier Ted wrote: Hi Roger, sorry for the delay - yeah, its not the formula that you sent that causes a problem or anything, its that the one formula (the original/initial formula that these others get the data from) can generate the #VALUE/0! error because it is dividing the answer of previous sums. If there is no data in one of those cells, then it hits a problem because it cant divide 0 by 0 etc; so gives the #VALUE/0! error message. Then, when when I ask for the ABS value to be displayed in a final set of cells, it carries the #VALUE/0! error with it (because its the content of its dependant cell). So, what I was hoping to do, is add something to the last or one from last formula to 'weed-out'/remove the error text/values, and replace them with nothing. This means that I need a formula that basically says: original formula " =ABS(DM23) " Plus 'IF answer = "#VALUE/0!" THEN give answer "" ' {where "" means blank space/empty cell etc} Any idea how I can do tis please??? Ted. "Roger Govier" wrote: Hi Ted I don't get any error with this formula. With 1 in H27 and Null in G48 from another formula, it returns 0. Send me a copy of your sheet directly and I will take a look for you. Remove NOSPAM from my address to send direct. Regards Roger Govier Ted wrote: Hi, no sorry, that doesnt work - I think it needs to be inc in the =IF(N(H27)=0,"",ROUND(H27*G$48,3)) formula as well, so that it is not affected by other formula, other than the absoult value one - any suggestions?? Thanks again, Ted. "Roger Govier" wrote: Hi Ted Perhaps =IF(OR(G23<G$45,G23G$46,G$47<1),"",IF(G$480, (G23-G$45)/G$47,(G$46-G23)/G$47)) Regards Roger Govier Ted wrote: Hi, The below formula =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) is used to generate data. If there is only one data in the set, then it returns a #VALUE! Error message (as expected) The below formula is then used to show the absolute value of that answer in another set of cells =IF(N(CO23)=0,"",(ABS(CO23))) The problem being, I do need the absolute value of the data from those cells, but not the error code because it mucks/stops up other formula: =MATCH(MAX(B52:AD52),B52:AD52,0) The cell references may differ, but the formula is the same for all<< Does anyone know of a way to get the second formula to change #VALUE! for an empty cell, as the answer, when the #VALUE! error is generated, but transfer all numbers (where there are any) in tact!?? Thanks in advance, Ted. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Problem - interrupted by #VALUE! in other cells!?
Hi Ted
I fully understand about the confidentiality of data. I am just concerned to know the contents of various cells. You posted =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) as giving a # VALUE error in any data is missing from the cells. I replied with a suggestion of =IF(OR(G23<G$45,G23G$46,G$47<1),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) which you say does not sort the problem (it didn't return an error for me with the test data I made up). Can you tell me then, what is in G23, G45, G46, G48 and G47. Also, you said that The below formula is then used to show the absolute value of that answer in another set of cells =IF(N(CO23)=0,"",(ABS(CO23))) from this I deduce that the formula above is in cell CO23, is this correct? and you say the error code because it mucks/stops up other formula: =MATCH(MAX(B52:AD52),B52:AD52,0) so I can't see why CO23 would affect this last formula which is only looking at row 52. Can you clarify? Regards Roger Govier Ted wrote: Hi Roger, thanks for getting back to me. Unfortunately I am very restricted in what I can send to you, as the database is part of a larger project, being conducted in a social science department of a University; where unfortunate, and very ridged guidelines apply. To make things more difficult still, the database contains actual subject data and information for me to delete this out, would then limit what can be seen, in terms of what has to be done to make the database perform as intended etc. Thank you for the offer though, it is appreciated. I apologise for the unusual situation, and hope there is a way you can still offer advice. With the term all I need being used very loosely; all I need really speaking is a formula that tells excel to show the absolute value of a cell (e.g. content of A1 displayed in A20). I need it to change any values that are anything but a number to a blank or a zero (blank is preferable); and need the formula to be encompassed in this formula: =ABS(A1) Thanks Roger, and sorry again for not being able to make things easier. Kind regards and speak soon, Ted. "Roger Govier" wrote: Hi Ted As I said, send me a copy. It is easier than trying to describe where all the potential pitfalls lie. Regards Roger Govier Ted wrote: Hi Roger, sorry for the delay - yeah, its not the formula that you sent that causes a problem or anything, its that the one formula (the original/initial formula that these others get the data from) can generate the #VALUE/0! error because it is dividing the answer of previous sums. If there is no data in one of those cells, then it hits a problem because it cant divide 0 by 0 etc; so gives the #VALUE/0! error message. Then, when when I ask for the ABS value to be displayed in a final set of cells, it carries the #VALUE/0! error with it (because its the content of its dependant cell). So, what I was hoping to do, is add something to the last or one from last formula to 'weed-out'/remove the error text/values, and replace them with nothing. This means that I need a formula that basically says: original formula " =ABS(DM23) " Plus 'IF answer = "#VALUE/0!" THEN give answer "" ' {where "" means blank space/empty cell etc} Any idea how I can do tis please??? Ted. "Roger Govier" wrote: Hi Ted I don't get any error with this formula. With 1 in H27 and Null in G48 from another formula, it returns 0. Send me a copy of your sheet directly and I will take a look for you. Remove NOSPAM from my address to send direct. Regards Roger Govier Ted wrote: Hi, no sorry, that doesnt work - I think it needs to be inc in the =IF(N(H27)=0,"",ROUND(H27*G$48,3)) formula as well, so that it is not affected by other formula, other than the absoult value one - any suggestions?? Thanks again, Ted. "Roger Govier" wrote: Hi Ted Perhaps =IF(OR(G23<G$45,G23G$46,G$47<1),"",IF(G$48 0,(G23-G$45)/G$47,(G$46-G23)/G$47)) Regards Roger Govier Ted wrote: Hi, The below formula =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G 23-G$45)/G$47,(G$46-G23)/G$47)) is used to generate data. If there is only one data in the set, then it returns a #VALUE! Error message (as expected) The below formula is then used to show the absolute value of that answer in another set of cells =IF(N(CO23)=0,"",(ABS(CO23))) The problem being, I do need the absolute value of the data from those cells, but not the error code because it mucks/stops up other formula: =MATCH(MAX(B52:AD52),B52:AD52,0) The cell references may differ, but the formula is the same for all<< Does anyone know of a way to get the second formula to change #VALUE! for an empty cell, as the answer, when the #VALUE! error is generated, but transfer all numbers (where there are any) in tact!?? Thanks in advance, Ted. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Problem - interrupted by #VALUE! in other cells!?
Hi Roger,
G23 = data returned by other formula, the rest of the cells are means, standard deviations and ranges. The reason why the #VALUE/0! is returned, I assume at least, is because at times there is only one entry in the dataset - part of the formula =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) is looking at means and things like that - things that are dependant on there being more than one entry in the dataset (hence not being able to divide by 0). the best place for something to be added to remove the #VALUE/0! error code is in either =ROUND(H23*G$48,3) (the additional bit you say on this formula was where I had tried to get around the above problem) or in the =(ABS(Q23)) formula. the final formula is affected because it is looking for the highest score, which it would appear is the #VALUE/0! error code! so to reduce the possibility of there being an effect on other formula, the favored place for a preventative formula to be added is in the =(ABS(xxx...) formula scenes its only function is to show the content of another cell/ set of cells. If there is a way, I just want to get excel to detect anything other than numbers, and transfer it to a blank or to a zero??? Thanks for your help $;-D I will be offline for the next 2hrs, but will be back online for a few hours then - speak soon and thanks again. Ted. "Roger Govier" wrote: Hi Ted I fully understand about the confidentiality of data. I am just concerned to know the contents of various cells. You posted =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) as giving a # VALUE error in any data is missing from the cells. I replied with a suggestion of =IF(OR(G23<G$45,G23G$46,G$47<1),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) which you say does not sort the problem (it didn't return an error for me with the test data I made up). Can you tell me then, what is in G23, G45, G46, G48 and G47. Also, you said that The below formula is then used to show the absolute value of that answer in another set of cells =IF(N(CO23)=0,"",(ABS(CO23))) from this I deduce that the formula above is in cell CO23, is this correct? and you say the error code because it mucks/stops up other formula: =MATCH(MAX(B52:AD52),B52:AD52,0) so I can't see why CO23 would affect this last formula which is only looking at row 52. Can you clarify? Regards Roger Govier Ted wrote: Hi Roger, thanks for getting back to me. Unfortunately I am very restricted in what I can send to you, as the database is part of a larger project, being conducted in a social science department of a University; where unfortunate, and very ridged guidelines apply. To make things more difficult still, the database contains actual subject data and information for me to delete this out, would then limit what can be seen, in terms of what has to be done to make the database perform as intended etc. Thank you for the offer though, it is appreciated. I apologise for the unusual situation, and hope there is a way you can still offer advice. With the term all I need being used very loosely; all I need really speaking is a formula that tells excel to show the absolute value of a cell (e.g. content of A1 displayed in A20). I need it to change any values that are anything but a number to a blank or a zero (blank is preferable); and need the formula to be encompassed in this formula: =ABS(A1) Thanks Roger, and sorry again for not being able to make things easier. Kind regards and speak soon, Ted. "Roger Govier" wrote: Hi Ted As I said, send me a copy. It is easier than trying to describe where all the potential pitfalls lie. Regards Roger Govier Ted wrote: Hi Roger, sorry for the delay - yeah, its not the formula that you sent that causes a problem or anything, its that the one formula (the original/initial formula that these others get the data from) can generate the #VALUE/0! error because it is dividing the answer of previous sums. If there is no data in one of those cells, then it hits a problem because it cant divide 0 by 0 etc; so gives the #VALUE/0! error message. Then, when when I ask for the ABS value to be displayed in a final set of cells, it carries the #VALUE/0! error with it (because its the content of its dependant cell). So, what I was hoping to do, is add something to the last or one from last formula to 'weed-out'/remove the error text/values, and replace them with nothing. This means that I need a formula that basically says: original formula " =ABS(DM23) " Plus 'IF answer = "#VALUE/0!" THEN give answer "" ' {where "" means blank space/empty cell etc} Any idea how I can do tis please??? Ted. "Roger Govier" wrote: Hi Ted I don't get any error with this formula. With 1 in H27 and Null in G48 from another formula, it returns 0. Send me a copy of your sheet directly and I will take a look for you. Remove NOSPAM from my address to send direct. Regards Roger Govier Ted wrote: Hi, no sorry, that doesnt work - I think it needs to be inc in the =IF(N(H27)=0,"",ROUND(H27*G$48,3)) formula as well, so that it is not affected by other formula, other than the absoult value one - any suggestions?? Thanks again, Ted. "Roger Govier" wrote: Hi Ted Perhaps =IF(OR(G23<G$45,G23G$46,G$47<1),"",IF(G$48 0,(G23-G$45)/G$47,(G$46-G23)/G$47)) Regards Roger Govier Ted wrote: Hi, The below formula =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G 23-G$45)/G$47,(G$46-G23)/G$47)) is used to generate data. If there is only one data in the set, then it returns a #VALUE! Error message (as expected) The below formula is then used to show the absolute value of that answer in another set of cells =IF(N(CO23)=0,"",(ABS(CO23))) The problem being, I do need the absolute value of the data from those cells, but not the error code because it mucks/stops up other formula: =MATCH(MAX(B52:AD52),B52:AD52,0) The cell references may differ, but the formula is the same for all<< Does anyone know of a way to get the second formula to change #VALUE! for an empty cell, as the answer, when the #VALUE! error is generated, but transfer all numbers (where there are any) in tact!?? Thanks in advance, Ted. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Problem - interrupted by #VALUE! in other cells!?
Hi Ted
so to reduce the possibility of there being an effect on other formula, the favored place for a preventative formula to be added is in the =(ABS(xxx...) I can't agree with you there. I think you need to ensure that any errors are trapped at their earliest possible occurrence, not way down the line through other formulae. You need to look at the formulae that give rise to the values in G23 etc. and trap there for the existence of sufficient values for the formula to be evaluated without giving rise to the #DIV/0 error. Post examples of the formulae that give rise to the values in the cells that I requested in my previous posting, then I (and/or others) can help you fix that problem. Regards Roger Govier Ted wrote: Hi Roger, G23 = data returned by other formula, the rest of the cells are means, standard deviations and ranges. The reason why the #VALUE/0! is returned, I assume at least, is because at times there is only one entry in the dataset - part of the formula =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) is looking at means and things like that - things that are dependant on there being more than one entry in the dataset (hence not being able to divide by 0). the best place for something to be added to remove the #VALUE/0! error code is in either =ROUND(H23*G$48,3) (the additional bit you say on this formula was where I had tried to get around the above problem) or in the =(ABS(Q23)) formula. the final formula is affected because it is looking for the highest score, which it would appear is the #VALUE/0! error code! so to reduce the possibility of there being an effect on other formula, the favored place for a preventative formula to be added is in the =(ABS(xxx...) formula scenes its only function is to show the content of another cell/ set of cells. If there is a way, I just want to get excel to detect anything other than numbers, and transfer it to a blank or to a zero??? Thanks for your help $;-D I will be offline for the next 2hrs, but will be back online for a few hours then - speak soon and thanks again. Ted. "Roger Govier" wrote: Hi Ted I fully understand about the confidentiality of data. I am just concerned to know the contents of various cells. You posted =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) as giving a # VALUE error in any data is missing from the cells. I replied with a suggestion of =IF(OR(G23<G$45,G23G$46,G$47<1),"",IF(G$480,(G 23-G$45)/G$47,(G$46-G23)/G$47)) which you say does not sort the problem (it didn't return an error for me with the test data I made up). Can you tell me then, what is in G23, G45, G46, G48 and G47. Also, you said that The below formula is then used to show the absolute value of that answer in another set of cells =IF(N(CO23)=0,"",(ABS(CO23))) from this I deduce that the formula above is in cell CO23, is this correct? and you say the error code because it mucks/stops up other formula: =MATCH(MAX(B52:AD52),B52:AD52,0) so I can't see why CO23 would affect this last formula which is only looking at row 52. Can you clarify? Regards Roger Govier Ted wrote: Hi Roger, thanks for getting back to me. Unfortunately I am very restricted in what I can send to you, as the database is part of a larger project, being conducted in a social science department of a University; where unfortunate, and very ridged guidelines apply. To make things more difficult still, the database contains actual subject data and information for me to delete this out, would then limit what can be seen, in terms of what has to be done to make the database perform as intended etc. Thank you for the offer though, it is appreciated. I apologise for the unusual situation, and hope there is a way you can still offer advice. With the term all I need being used very loosely; all I need really speaking is a formula that tells excel to show the absolute value of a cell (e.g. content of A1 displayed in A20). I need it to change any values that are anything but a number to a blank or a zero (blank is preferable); and need the formula to be encompassed in this formula: =ABS(A1) Thanks Roger, and sorry again for not being able to make things easier. Kind regards and speak soon, Ted. "Roger Govier" wrote: Hi Ted As I said, send me a copy. It is easier than trying to describe where all the potential pitfalls lie. Regards Roger Govier Ted wrote: Hi Roger, sorry for the delay - yeah, its not the formula that you sent that causes a problem or anything, its that the one formula (the original/initial formula that these others get the data from) can generate the #VALUE/0! error because it is dividing the answer of previous sums. If there is no data in one of those cells, then it hits a problem because it cant divide 0 by 0 etc; so gives the #VALUE/0! error message. Then, when when I ask for the ABS value to be displayed in a final set of cells, it carries the #VALUE/0! error with it (because its the content of its dependant cell). So, what I was hoping to do, is add something to the last or one from last formula to 'weed-out'/remove the error text/values, and replace them with nothing. This means that I need a formula that basically says: original formula " =ABS(DM23) " Plus 'IF answer = "#VALUE/0!" THEN give answer "" ' {where "" means blank space/empty cell etc} Any idea how I can do tis please??? Ted. "Roger Govier" wrote: Hi Ted I don't get any error with this formula. With 1 in H27 and Null in G48 from another formula, it returns 0. Send me a copy of your sheet directly and I will take a look for you. Remove NOSPAM from my address to send direct. Regards Roger Govier Ted wrote: Hi, no sorry, that doesnt work - I think it needs to be inc in the =IF(N(H27)=0,"",ROUND(H27*G$48,3)) formula as well, so that it is not affected by other formula, other than the absoult value one - any suggestions?? Thanks again, Ted. "Roger Govier" wrote: Hi Ted Perhaps =IF(OR(G23<G$45,G23G$46,G$47<1),"",IF(G$4 80,(G23-G$45)/G$47,(G$46-G23)/G$47)) Regards Roger Govier Ted wrote: Hi, The below formula =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G 23-G$45)/G$47,(G$46-G23)/G$47)) is used to generate data. If there is only one data in the set, then it returns a #VALUE! Error message (as expected) The below formula is then used to show the absolute value of that answer in another set of cells =IF(N(CO23)=0,"",(ABS(CO23))) The problem being, I do need the absolute value of the data from those cells, but not the error code because it mucks/stops up other formula: =MATCH(MAX(B52:AD52),B52:AD52,0) The cell references may differ, but the formula is the same for all<< Does anyone know of a way to get the second formula to change #VALUE! for an empty cell, as the answer, when the #VALUE! error is generated, but transfer all numbers (where there are any) in tact!?? Thanks in advance, Ted. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Problem - interrupted by #VALUE! in other cells!?
Hi Roger, as I say, the formula
=IF(OR(G24<G$45,G24G$46),"",IF(G$480,(G24-G$45)/G$47,(G$46-G24)/G$47)) causes the error as it is dependant on cells which look at standard deviation, means and stuff like that (in G45:48) - if there is only one data entry, then these values cannot be worked out, so it generates the #VALUE/0! error. That part is ok and is acceptable. But the final ABS formula takes the error codes to a set of cells, where the largest is highlighted using =MATCH(MAX(B50:AD50),B50:AD50,0). If ABS brings an error code to the B50:AD50 cells, then the MAX formula classes them as the greatest value. Because the formulas prior to the =(ABS(... formula are all dependant on means and standard deviations, there is no way to prevent the error from being generated when only one data is available. as far as analysis goes, it does not matter either as any missing data does not matter because the number of subjects evens that sort of stuff out. thats why I think that the best place might be the ABS formula - what do you think?? Also, prior formula is also dependant on correlations within the G40-odd cells. again, meaning that error codes are generated, as expected (that part is fully acceptable though, fortunatelly). Let me know what oyu think please, and thanks again for oyur help. P.S. sorry for the extended 2hrs. Ted. "Roger Govier" wrote: Hi Ted so to reduce the possibility of there being an effect on other formula, the favored place for a preventative formula to be added is in the =(ABS(xxx...) I can't agree with you there. I think you need to ensure that any errors are trapped at their earliest possible occurrence, not way down the line through other formulae. You need to look at the formulae that give rise to the values in G23 etc. and trap there for the existence of sufficient values for the formula to be evaluated without giving rise to the #DIV/0 error. Post examples of the formulae that give rise to the values in the cells that I requested in my previous posting, then I (and/or others) can help you fix that problem. Regards Roger Govier Ted wrote: Hi Roger, G23 = data returned by other formula, the rest of the cells are means, standard deviations and ranges. The reason why the #VALUE/0! is returned, I assume at least, is because at times there is only one entry in the dataset - part of the formula =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) is looking at means and things like that - things that are dependant on there being more than one entry in the dataset (hence not being able to divide by 0). the best place for something to be added to remove the #VALUE/0! error code is in either =ROUND(H23*G$48,3) (the additional bit you say on this formula was where I had tried to get around the above problem) or in the =(ABS(Q23)) formula. the final formula is affected because it is looking for the highest score, which it would appear is the #VALUE/0! error code! so to reduce the possibility of there being an effect on other formula, the favored place for a preventative formula to be added is in the =(ABS(xxx...) formula scenes its only function is to show the content of another cell/ set of cells. If there is a way, I just want to get excel to detect anything other than numbers, and transfer it to a blank or to a zero??? Thanks for your help $;-D I will be offline for the next 2hrs, but will be back online for a few hours then - speak soon and thanks again. Ted. "Roger Govier" wrote: Hi Ted I fully understand about the confidentiality of data. I am just concerned to know the contents of various cells. You posted =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) as giving a # VALUE error in any data is missing from the cells. I replied with a suggestion of =IF(OR(G23<G$45,G23G$46,G$47<1),"",IF(G$480,(G 23-G$45)/G$47,(G$46-G23)/G$47)) which you say does not sort the problem (it didn't return an error for me with the test data I made up). Can you tell me then, what is in G23, G45, G46, G48 and G47. Also, you said that The below formula is then used to show the absolute value of that answer in another set of cells =IF(N(CO23)=0,"",(ABS(CO23))) from this I deduce that the formula above is in cell CO23, is this correct? and you say the error code because it mucks/stops up other formula: =MATCH(MAX(B52:AD52),B52:AD52,0) so I can't see why CO23 would affect this last formula which is only looking at row 52. Can you clarify? Regards Roger Govier Ted wrote: Hi Roger, thanks for getting back to me. Unfortunately I am very restricted in what I can send to you, as the database is part of a larger project, being conducted in a social science department of a University; where unfortunate, and very ridged guidelines apply. To make things more difficult still, the database contains actual subject data and information for me to delete this out, would then limit what can be seen, in terms of what has to be done to make the database perform as intended etc. Thank you for the offer though, it is appreciated. I apologise for the unusual situation, and hope there is a way you can still offer advice. With the term all I need being used very loosely; all I need really speaking is a formula that tells excel to show the absolute value of a cell (e.g. content of A1 displayed in A20). I need it to change any values that are anything but a number to a blank or a zero (blank is preferable); and need the formula to be encompassed in this formula: =ABS(A1) Thanks Roger, and sorry again for not being able to make things easier. Kind regards and speak soon, Ted. "Roger Govier" wrote: Hi Ted As I said, send me a copy. It is easier than trying to describe where all the potential pitfalls lie. Regards Roger Govier Ted wrote: Hi Roger, sorry for the delay - yeah, its not the formula that you sent that causes a problem or anything, its that the one formula (the original/initial formula that these others get the data from) can generate the #VALUE/0! error because it is dividing the answer of previous sums. If there is no data in one of those cells, then it hits a problem because it cant divide 0 by 0 etc; so gives the #VALUE/0! error message. Then, when when I ask for the ABS value to be displayed in a final set of cells, it carries the #VALUE/0! error with it (because its the content of its dependant cell). So, what I was hoping to do, is add something to the last or one from last formula to 'weed-out'/remove the error text/values, and replace them with nothing. This means that I need a formula that basically says: original formula " =ABS(DM23) " Plus 'IF answer = "#VALUE/0!" THEN give answer "" ' {where "" means blank space/empty cell etc} Any idea how I can do tis please??? Ted. "Roger Govier" wrote: Hi Ted I don't get any error with this formula. With 1 in H27 and Null in G48 from another formula, it returns 0. Send me a copy of your sheet directly and I will take a look for you. Remove NOSPAM from my address to send direct. Regards Roger Govier Ted wrote: Hi, no sorry, that doesnt work - I think it needs to be inc in the =IF(N(H27)=0,"",ROUND(H27*G$48,3)) formula as well, so that it is not affected by other formula, other than the absoult value one - any suggestions?? Thanks again, Ted. "Roger Govier" wrote: Hi Ted Perhaps =IF(OR(G23<G$45,G23G$46,G$47<1),"",IF(G$4 80,(G23-G$45)/G$47,(G$46-G23)/G$47)) Regards Roger Govier Ted wrote: Hi, The below formula =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G 23-G$45)/G$47,(G$46-G23)/G$47)) is used to generate data. If there is only one data in the set, then it returns a #VALUE! Error message (as expected) The below formula is then used to show the absolute value of that answer in another set of cells =IF(N(CO23)=0,"",(ABS(CO23))) The problem being, I do need the absolute value of the data from those cells, but not the error code because it mucks/stops up other formula: =MATCH(MAX(B52:AD52),B52:AD52,0) The cell references may differ, but the formula is the same for all<< Does anyone know of a way to get the second formula to change #VALUE! for an empty cell, as the answer, when the #VALUE! error is generated, but transfer all numbers (where there are any) in tact!?? Thanks in advance, Ted. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Problem - interrupted by #VALUE! in other cells!?
Hi Roger, just to let you know, I have worked it out by using:
=IF(ISERROR(ABS(A50)),"",ABS(A50)) thank you for your help though $;-) thanks again, Ted. "Roger Govier" wrote: Hi Ted so to reduce the possibility of there being an effect on other formula, the favored place for a preventative formula to be added is in the =(ABS(xxx...) I can't agree with you there. I think you need to ensure that any errors are trapped at their earliest possible occurrence, not way down the line through other formulae. You need to look at the formulae that give rise to the values in G23 etc. and trap there for the existence of sufficient values for the formula to be evaluated without giving rise to the #DIV/0 error. Post examples of the formulae that give rise to the values in the cells that I requested in my previous posting, then I (and/or others) can help you fix that problem. Regards Roger Govier Ted wrote: Hi Roger, G23 = data returned by other formula, the rest of the cells are means, standard deviations and ranges. The reason why the #VALUE/0! is returned, I assume at least, is because at times there is only one entry in the dataset - part of the formula =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) is looking at means and things like that - things that are dependant on there being more than one entry in the dataset (hence not being able to divide by 0). the best place for something to be added to remove the #VALUE/0! error code is in either =ROUND(H23*G$48,3) (the additional bit you say on this formula was where I had tried to get around the above problem) or in the =(ABS(Q23)) formula. the final formula is affected because it is looking for the highest score, which it would appear is the #VALUE/0! error code! so to reduce the possibility of there being an effect on other formula, the favored place for a preventative formula to be added is in the =(ABS(xxx...) formula scenes its only function is to show the content of another cell/ set of cells. If there is a way, I just want to get excel to detect anything other than numbers, and transfer it to a blank or to a zero??? Thanks for your help $;-D I will be offline for the next 2hrs, but will be back online for a few hours then - speak soon and thanks again. Ted. "Roger Govier" wrote: Hi Ted I fully understand about the confidentiality of data. I am just concerned to know the contents of various cells. You posted =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) as giving a # VALUE error in any data is missing from the cells. I replied with a suggestion of =IF(OR(G23<G$45,G23G$46,G$47<1),"",IF(G$480,(G 23-G$45)/G$47,(G$46-G23)/G$47)) which you say does not sort the problem (it didn't return an error for me with the test data I made up). Can you tell me then, what is in G23, G45, G46, G48 and G47. Also, you said that The below formula is then used to show the absolute value of that answer in another set of cells =IF(N(CO23)=0,"",(ABS(CO23))) from this I deduce that the formula above is in cell CO23, is this correct? and you say the error code because it mucks/stops up other formula: =MATCH(MAX(B52:AD52),B52:AD52,0) so I can't see why CO23 would affect this last formula which is only looking at row 52. Can you clarify? Regards Roger Govier Ted wrote: Hi Roger, thanks for getting back to me. Unfortunately I am very restricted in what I can send to you, as the database is part of a larger project, being conducted in a social science department of a University; where unfortunate, and very ridged guidelines apply. To make things more difficult still, the database contains actual subject data and information for me to delete this out, would then limit what can be seen, in terms of what has to be done to make the database perform as intended etc. Thank you for the offer though, it is appreciated. I apologise for the unusual situation, and hope there is a way you can still offer advice. With the term all I need being used very loosely; all I need really speaking is a formula that tells excel to show the absolute value of a cell (e.g. content of A1 displayed in A20). I need it to change any values that are anything but a number to a blank or a zero (blank is preferable); and need the formula to be encompassed in this formula: =ABS(A1) Thanks Roger, and sorry again for not being able to make things easier. Kind regards and speak soon, Ted. "Roger Govier" wrote: Hi Ted As I said, send me a copy. It is easier than trying to describe where all the potential pitfalls lie. Regards Roger Govier Ted wrote: Hi Roger, sorry for the delay - yeah, its not the formula that you sent that causes a problem or anything, its that the one formula (the original/initial formula that these others get the data from) can generate the #VALUE/0! error because it is dividing the answer of previous sums. If there is no data in one of those cells, then it hits a problem because it cant divide 0 by 0 etc; so gives the #VALUE/0! error message. Then, when when I ask for the ABS value to be displayed in a final set of cells, it carries the #VALUE/0! error with it (because its the content of its dependant cell). So, what I was hoping to do, is add something to the last or one from last formula to 'weed-out'/remove the error text/values, and replace them with nothing. This means that I need a formula that basically says: original formula " =ABS(DM23) " Plus 'IF answer = "#VALUE/0!" THEN give answer "" ' {where "" means blank space/empty cell etc} Any idea how I can do tis please??? Ted. "Roger Govier" wrote: Hi Ted I don't get any error with this formula. With 1 in H27 and Null in G48 from another formula, it returns 0. Send me a copy of your sheet directly and I will take a look for you. Remove NOSPAM from my address to send direct. Regards Roger Govier Ted wrote: Hi, no sorry, that doesnt work - I think it needs to be inc in the =IF(N(H27)=0,"",ROUND(H27*G$48,3)) formula as well, so that it is not affected by other formula, other than the absoult value one - any suggestions?? Thanks again, Ted. "Roger Govier" wrote: Hi Ted Perhaps =IF(OR(G23<G$45,G23G$46,G$47<1),"",IF(G$4 80,(G23-G$45)/G$47,(G$46-G23)/G$47)) Regards Roger Govier Ted wrote: Hi, The below formula =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G 23-G$45)/G$47,(G$46-G23)/G$47)) is used to generate data. If there is only one data in the set, then it returns a #VALUE! Error message (as expected) The below formula is then used to show the absolute value of that answer in another set of cells =IF(N(CO23)=0,"",(ABS(CO23))) The problem being, I do need the absolute value of the data from those cells, but not the error code because it mucks/stops up other formula: =MATCH(MAX(B52:AD52),B52:AD52,0) The cell references may differ, but the formula is the same for all<< Does anyone know of a way to get the second formula to change #VALUE! for an empty cell, as the answer, when the #VALUE! error is generated, but transfer all numbers (where there are any) in tact!?? Thanks in advance, Ted. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Problem - interrupted by #VALUE! in other cells!?
Hi Ted
I'm glad you have got it worked out to your satisfaction. However, I still believe that intrinsically, all errors should be trapped at root. Allowing them to pervade through a series of further calculations before finally trapping them, COULD result in erroneous conclusions. You have still not said what these earlier formulae are, other than "means, SD's and the like",and I am sure it would be possible to trap errors at this level, which MAY have a bearing upon later outcomes. Still, its your call. If you are based in the States Ted, enjoy the remainder of Thanksgiving. Regards Roger Govier Ted wrote: Hi Roger, just to let you know, I have worked it out by using: =IF(ISERROR(ABS(A50)),"",ABS(A50)) thank you for your help though $;-) thanks again, Ted. "Roger Govier" wrote: Hi Ted so to reduce the possibility of there being an effect on other formula, the favored place for a preventative formula to be added is in the =(ABS(xxx...) I can't agree with you there. I think you need to ensure that any errors are trapped at their earliest possible occurrence, not way down the line through other formulae. You need to look at the formulae that give rise to the values in G23 etc. and trap there for the existence of sufficient values for the formula to be evaluated without giving rise to the #DIV/0 error. Post examples of the formulae that give rise to the values in the cells that I requested in my previous posting, then I (and/or others) can help you fix that problem. Regards Roger Govier Ted wrote: Hi Roger, G23 = data returned by other formula, the rest of the cells are means, standard deviations and ranges. The reason why the #VALUE/0! is returned, I assume at least, is because at times there is only one entry in the dataset - part of the formula =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) is looking at means and things like that - things that are dependant on there being more than one entry in the dataset (hence not being able to divide by 0). the best place for something to be added to remove the #VALUE/0! error code is in either =ROUND(H23*G$48,3) (the additional bit you say on this formula was where I had tried to get around the above problem) or in the =(ABS(Q23)) formula. the final formula is affected because it is looking for the highest score, which it would appear is the #VALUE/0! error code! so to reduce the possibility of there being an effect on other formula, the favored place for a preventative formula to be added is in the =(ABS(xxx...) formula scenes its only function is to show the content of another cell/ set of cells. If there is a way, I just want to get excel to detect anything other than numbers, and transfer it to a blank or to a zero??? Thanks for your help $;-D I will be offline for the next 2hrs, but will be back online for a few hours then - speak soon and thanks again. Ted. "Roger Govier" wrote: Hi Ted I fully understand about the confidentiality of data. I am just concerned to know the contents of various cells. You posted =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) as giving a # VALUE error in any data is missing from the cells. I replied with a suggestion of =IF(OR(G23<G$45,G23G$46,G$47<1),"",IF(G$480, (G23-G$45)/G$47,(G$46-G23)/G$47)) which you say does not sort the problem (it didn't return an error for me with the test data I made up). Can you tell me then, what is in G23, G45, G46, G48 and G47. Also, you said that The below formula is then used to show the absolute value of that answer in another set of cells =IF(N(CO23)=0,"",(ABS(CO23))) from this I deduce that the formula above is in cell CO23, is this correct? and you say the error code because it mucks/stops up other formula: =MATCH(MAX(B52:AD52),B52:AD52,0) so I can't see why CO23 would affect this last formula which is only looking at row 52. Can you clarify? Regards Roger Govier Ted wrote: Hi Roger, thanks for getting back to me. Unfortunately I am very restricted in what I can send to you, as the database is part of a larger project, being conducted in a social science department of a University; where unfortunate, and very ridged guidelines apply. To make things more difficult still, the database contains actual subject data and information for me to delete this out, would then limit what can be seen, in terms of what has to be done to make the database perform as intended etc. Thank you for the offer though, it is appreciated. I apologise for the unusual situation, and hope there is a way you can still offer advice. With the term all I need being used very loosely; all I need really speaking is a formula that tells excel to show the absolute value of a cell (e.g. content of A1 displayed in A20). I need it to change any values that are anything but a number to a blank or a zero (blank is preferable); and need the formula to be encompassed in this formula: =ABS(A1) Thanks Roger, and sorry again for not being able to make things easier. Kind regards and speak soon, Ted. "Roger Govier" wrote: Hi Ted As I said, send me a copy. It is easier than trying to describe where all the potential pitfalls lie. Regards Roger Govier Ted wrote: Hi Roger, sorry for the delay - yeah, its not the formula that you sent that causes a problem or anything, its that the one formula (the original/initial formula that these others get the data from) can generate the #VALUE/0! error because it is dividing the answer of previous sums. If there is no data in one of those cells, then it hits a problem because it cant divide 0 by 0 etc; so gives the #VALUE/0! error message. Then, when when I ask for the ABS value to be displayed in a final set of cells, it carries the #VALUE/0! error with it (because its the content of its dependant cell). So, what I was hoping to do, is add something to the last or one from last formula to 'weed-out'/remove the error text/values, and replace them with nothing. This means that I need a formula that basically says: original formula " =ABS(DM23) " Plus 'IF answer = "#VALUE/0!" THEN give answer "" ' {where "" means blank space/empty cell etc} Any idea how I can do tis please??? Ted. "Roger Govier" wrote: Hi Ted I don't get any error with this formula. With 1 in H27 and Null in G48 from another formula, it returns 0. Send me a copy of your sheet directly and I will take a look for you. Remove NOSPAM from my address to send direct. Regards Roger Govier Ted wrote: Hi, no sorry, that doesnt work - I think it needs to be inc in the =IF(N(H27)=0,"",ROUND(H27*G$48,3)) formula as well, so that it is not affected by other formula, other than the absoult value one - any suggestions?? Thanks again, Ted. "Roger Govier" wrote: Hi Ted Perhaps =IF(OR(G23<G$45,G23G$46,G$47<1),"",IF(G $480,(G23-G$45)/G$47,(G$46-G23)/G$47)) Regards Roger Govier Ted wrote: Hi, The below formula =IF(OR(G23<G$45,G23G$46),"",IF(G$480, (G23-G$45)/G$47,(G$46-G23)/G$47)) is used to generate data. If there is only one data in the set, then it returns a #VALUE! Error message (as expected) The below formula is then used to show the absolute value of that answer in another set of cells =IF(N(CO23)=0,"",(ABS(CO23))) The problem being, I do need the absolute value of the data from those cells, but not the error code because it mucks/stops up other formula: =MATCH(MAX(B52:AD52),B52:AD52,0) The cell references may differ, but the formula is the same for all<< Does anyone know of a way to get the second formula to change #VALUE! for an empty cell, as the answer, when the #VALUE! error is generated, but transfer all numbers (where there are any) in tact!?? Thanks in advance, Ted. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Problem - interrupted by #VALUE! in other cells!?
Hi Roger, I'm construction a reply now, giving further details - speak in a
few min... Ted. P.S. Happy Thanksgiving too "Roger Govier" wrote: Hi Ted I'm glad you have got it worked out to your satisfaction. However, I still believe that intrinsically, all errors should be trapped at root. Allowing them to pervade through a series of further calculations before finally trapping them, COULD result in erroneous conclusions. You have still not said what these earlier formulae are, other than "means, SD's and the like",and I am sure it would be possible to trap errors at this level, which MAY have a bearing upon later outcomes. Still, its your call. If you are based in the States Ted, enjoy the remainder of Thanksgiving. Regards Roger Govier Ted wrote: Hi Roger, just to let you know, I have worked it out by using: =IF(ISERROR(ABS(A50)),"",ABS(A50)) thank you for your help though $;-) thanks again, Ted. "Roger Govier" wrote: Hi Ted so to reduce the possibility of there being an effect on other formula, the favored place for a preventative formula to be added is in the =(ABS(xxx...) I can't agree with you there. I think you need to ensure that any errors are trapped at their earliest possible occurrence, not way down the line through other formulae. You need to look at the formulae that give rise to the values in G23 etc. and trap there for the existence of sufficient values for the formula to be evaluated without giving rise to the #DIV/0 error. Post examples of the formulae that give rise to the values in the cells that I requested in my previous posting, then I (and/or others) can help you fix that problem. Regards Roger Govier Ted wrote: Hi Roger, G23 = data returned by other formula, the rest of the cells are means, standard deviations and ranges. The reason why the #VALUE/0! is returned, I assume at least, is because at times there is only one entry in the dataset - part of the formula =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) is looking at means and things like that - things that are dependant on there being more than one entry in the dataset (hence not being able to divide by 0). the best place for something to be added to remove the #VALUE/0! error code is in either =ROUND(H23*G$48,3) (the additional bit you say on this formula was where I had tried to get around the above problem) or in the =(ABS(Q23)) formula. the final formula is affected because it is looking for the highest score, which it would appear is the #VALUE/0! error code! so to reduce the possibility of there being an effect on other formula, the favored place for a preventative formula to be added is in the =(ABS(xxx...) formula scenes its only function is to show the content of another cell/ set of cells. If there is a way, I just want to get excel to detect anything other than numbers, and transfer it to a blank or to a zero??? Thanks for your help $;-D I will be offline for the next 2hrs, but will be back online for a few hours then - speak soon and thanks again. Ted. "Roger Govier" wrote: Hi Ted I fully understand about the confidentiality of data. I am just concerned to know the contents of various cells. You posted =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) as giving a # VALUE error in any data is missing from the cells. I replied with a suggestion of =IF(OR(G23<G$45,G23G$46,G$47<1),"",IF(G$480, (G23-G$45)/G$47,(G$46-G23)/G$47)) which you say does not sort the problem (it didn't return an error for me with the test data I made up). Can you tell me then, what is in G23, G45, G46, G48 and G47. Also, you said that The below formula is then used to show the absolute value of that answer in another set of cells =IF(N(CO23)=0,"",(ABS(CO23))) from this I deduce that the formula above is in cell CO23, is this correct? and you say the error code because it mucks/stops up other formula: =MATCH(MAX(B52:AD52),B52:AD52,0) so I can't see why CO23 would affect this last formula which is only looking at row 52. Can you clarify? Regards Roger Govier Ted wrote: Hi Roger, thanks for getting back to me. Unfortunately I am very restricted in what I can send to you, as the database is part of a larger project, being conducted in a social science department of a University; where unfortunate, and very ridged guidelines apply. To make things more difficult still, the database contains actual subject data and information for me to delete this out, would then limit what can be seen, in terms of what has to be done to make the database perform as intended etc. Thank you for the offer though, it is appreciated. I apologise for the unusual situation, and hope there is a way you can still offer advice. With the term all I need being used very loosely; all I need really speaking is a formula that tells excel to show the absolute value of a cell (e.g. content of A1 displayed in A20). I need it to change any values that are anything but a number to a blank or a zero (blank is preferable); and need the formula to be encompassed in this formula: =ABS(A1) Thanks Roger, and sorry again for not being able to make things easier. Kind regards and speak soon, Ted. "Roger Govier" wrote: Hi Ted As I said, send me a copy. It is easier than trying to describe where all the potential pitfalls lie. Regards Roger Govier Ted wrote: Hi Roger, sorry for the delay - yeah, its not the formula that you sent that causes a problem or anything, its that the one formula (the original/initial formula that these others get the data from) can generate the #VALUE/0! error because it is dividing the answer of previous sums. If there is no data in one of those cells, then it hits a problem because it cant divide 0 by 0 etc; so gives the #VALUE/0! error message. Then, when when I ask for the ABS value to be displayed in a final set of cells, it carries the #VALUE/0! error with it (because its the content of its dependant cell). So, what I was hoping to do, is add something to the last or one from last formula to 'weed-out'/remove the error text/values, and replace them with nothing. This means that I need a formula that basically says: original formula " =ABS(DM23) " Plus 'IF answer = "#VALUE/0!" THEN give answer "" ' {where "" means blank space/empty cell etc} Any idea how I can do tis please??? Ted. "Roger Govier" wrote: Hi Ted I don't get any error with this formula. With 1 in H27 and Null in G48 from another formula, it returns 0. Send me a copy of your sheet directly and I will take a look for you. Remove NOSPAM from my address to send direct. Regards Roger Govier Ted wrote: Hi, no sorry, that doesnt work - I think it needs to be inc in the =IF(N(H27)=0,"",ROUND(H27*G$48,3)) formula as well, so that it is not affected by other formula, other than the absoult value one - any suggestions?? Thanks again, Ted. "Roger Govier" wrote: Hi Ted Perhaps =IF(OR(G23<G$45,G23G$46,G$47<1),"",IF(G $480,(G23-G$45)/G$47,(G$46-G23)/G$47)) Regards Roger Govier Ted wrote: Hi, The below formula =IF(OR(G23<G$45,G23G$46),"",IF(G$480, (G23-G$45)/G$47,(G$46-G23)/G$47)) is used to generate data. If there is only one data in the set, then it returns a #VALUE! Error message (as expected) The below formula is then used to show the absolute value of that answer in another set of cells =IF(N(CO23)=0,"",(ABS(CO23))) The problem being, I do need the absolute value of the data from those cells, but not the error code because it mucks/stops up other formula: =MATCH(MAX(B52:AD52),B52:AD52,0) The cell references may differ, but the formula is the same for all<< Does anyone know of a way to get the second formula to change #VALUE! for an empty cell, as the answer, when the #VALUE! error is generated, but transfer all numbers (where there are any) in tact!?? Thanks in advance, Ted. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Problem - interrupted by #VALUE! in other cells!?
Hi Roger, here is a list of what is in each cell respectively:
For the formula =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) G23 - data G45 mean G46 standard deviation G47 1st range G48 2nd range So as you can seen because of the ¦,(G23-G$45)/G$47,.. part, when there is no data in G23, and excel goes to perform the division between mean/1st range, it cant do it and generates a #VALUE/0! Error. The data or/and error codes are then picked up by this formula in cell H23:25 etc =IF(N(H23)=0,"",ROUND(H23*G$48,3)) the above gets rid of any zeros and replaces them with empty cells/blank spaces in cell I23. The data generated by that formula is then transferred to a different part of the spreadsheet, using the new formula of =IF(ISERROR(ABS(I23)),"",ABS(I23)) {the one I was having trouble with} All data is then arranged in a line, so the greatest value can be highlighted, using this formula =MATCH(MAX(A50:Z50),A50:Z50,0) This last formula is where the #VALUE/0! became a problem, because it classes text as the greatest value (or more specifically, #DIV/0! and #VALUE/0!). Meaning that by removing anything that isnt a number becomes a necessity, otherwise you are forced to manually remove them from the arranged results. The IF(ISERROR is now stopping the text/error codes from progressing as far as the final tables, so to speak; resulting in =, what at least appears to be, success! What say you?? Ted. "Roger Govier" wrote: Hi Ted I'm glad you have got it worked out to your satisfaction. However, I still believe that intrinsically, all errors should be trapped at root. Allowing them to pervade through a series of further calculations before finally trapping them, COULD result in erroneous conclusions. You have still not said what these earlier formulae are, other than "means, SD's and the like",and I am sure it would be possible to trap errors at this level, which MAY have a bearing upon later outcomes. Still, its your call. If you are based in the States Ted, enjoy the remainder of Thanksgiving. Regards Roger Govier Ted wrote: Hi Roger, just to let you know, I have worked it out by using: =IF(ISERROR(ABS(A50)),"",ABS(A50)) thank you for your help though $;-) thanks again, Ted. "Roger Govier" wrote: Hi Ted so to reduce the possibility of there being an effect on other formula, the favored place for a preventative formula to be added is in the =(ABS(xxx...) I can't agree with you there. I think you need to ensure that any errors are trapped at their earliest possible occurrence, not way down the line through other formulae. You need to look at the formulae that give rise to the values in G23 etc. and trap there for the existence of sufficient values for the formula to be evaluated without giving rise to the #DIV/0 error. Post examples of the formulae that give rise to the values in the cells that I requested in my previous posting, then I (and/or others) can help you fix that problem. Regards Roger Govier Ted wrote: Hi Roger, G23 = data returned by other formula, the rest of the cells are means, standard deviations and ranges. The reason why the #VALUE/0! is returned, I assume at least, is because at times there is only one entry in the dataset - part of the formula =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) is looking at means and things like that - things that are dependant on there being more than one entry in the dataset (hence not being able to divide by 0). the best place for something to be added to remove the #VALUE/0! error code is in either =ROUND(H23*G$48,3) (the additional bit you say on this formula was where I had tried to get around the above problem) or in the =(ABS(Q23)) formula. the final formula is affected because it is looking for the highest score, which it would appear is the #VALUE/0! error code! so to reduce the possibility of there being an effect on other formula, the favored place for a preventative formula to be added is in the =(ABS(xxx...) formula scenes its only function is to show the content of another cell/ set of cells. If there is a way, I just want to get excel to detect anything other than numbers, and transfer it to a blank or to a zero??? Thanks for your help $;-D I will be offline for the next 2hrs, but will be back online for a few hours then - speak soon and thanks again. Ted. "Roger Govier" wrote: Hi Ted I fully understand about the confidentiality of data. I am just concerned to know the contents of various cells. You posted =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) as giving a # VALUE error in any data is missing from the cells. I replied with a suggestion of =IF(OR(G23<G$45,G23G$46,G$47<1),"",IF(G$480, (G23-G$45)/G$47,(G$46-G23)/G$47)) which you say does not sort the problem (it didn't return an error for me with the test data I made up). Can you tell me then, what is in G23, G45, G46, G48 and G47. Also, you said that The below formula is then used to show the absolute value of that answer in another set of cells =IF(N(CO23)=0,"",(ABS(CO23))) from this I deduce that the formula above is in cell CO23, is this correct? and you say the error code because it mucks/stops up other formula: =MATCH(MAX(B52:AD52),B52:AD52,0) so I can't see why CO23 would affect this last formula which is only looking at row 52. Can you clarify? Regards Roger Govier Ted wrote: Hi Roger, thanks for getting back to me. Unfortunately I am very restricted in what I can send to you, as the database is part of a larger project, being conducted in a social science department of a University; where unfortunate, and very ridged guidelines apply. To make things more difficult still, the database contains actual subject data and information for me to delete this out, would then limit what can be seen, in terms of what has to be done to make the database perform as intended etc. Thank you for the offer though, it is appreciated. I apologise for the unusual situation, and hope there is a way you can still offer advice. With the term all I need being used very loosely; all I need really speaking is a formula that tells excel to show the absolute value of a cell (e.g. content of A1 displayed in A20). I need it to change any values that are anything but a number to a blank or a zero (blank is preferable); and need the formula to be encompassed in this formula: =ABS(A1) Thanks Roger, and sorry again for not being able to make things easier. Kind regards and speak soon, Ted. "Roger Govier" wrote: Hi Ted As I said, send me a copy. It is easier than trying to describe where all the potential pitfalls lie. Regards Roger Govier Ted wrote: Hi Roger, sorry for the delay - yeah, its not the formula that you sent that causes a problem or anything, its that the one formula (the original/initial formula that these others get the data from) can generate the #VALUE/0! error because it is dividing the answer of previous sums. If there is no data in one of those cells, then it hits a problem because it cant divide 0 by 0 etc; so gives the #VALUE/0! error message. Then, when when I ask for the ABS value to be displayed in a final set of cells, it carries the #VALUE/0! error with it (because its the content of its dependant cell). So, what I was hoping to do, is add something to the last or one from last formula to 'weed-out'/remove the error text/values, and replace them with nothing. This means that I need a formula that basically says: original formula " =ABS(DM23) " Plus 'IF answer = "#VALUE/0!" THEN give answer "" ' {where "" means blank space/empty cell etc} Any idea how I can do tis please??? Ted. "Roger Govier" wrote: Hi Ted I don't get any error with this formula. With 1 in H27 and Null in G48 from another formula, it returns 0. Send me a copy of your sheet directly and I will take a look for you. Remove NOSPAM from my address to send direct. Regards Roger Govier Ted wrote: Hi, no sorry, that doesnt work - I think it needs to be inc in the =IF(N(H27)=0,"",ROUND(H27*G$48,3)) formula as well, so that it is not affected by other formula, other than the absoult value one - any suggestions?? Thanks again, Ted. "Roger Govier" wrote: Hi Ted Perhaps =IF(OR(G23<G$45,G23G$46,G$47<1),"",IF(G $480,(G23-G$45)/G$47,(G$46-G23)/G$47)) Regards Roger Govier Ted wrote: Hi, The below formula =IF(OR(G23<G$45,G23G$46),"",IF(G$480, (G23-G$45)/G$47,(G$46-G23)/G$47)) is used to generate data. If there is only one data in the set, then it returns a #VALUE! Error message (as expected) The below formula is then used to show the absolute value of that answer in another set of cells =IF(N(CO23)=0,"",(ABS(CO23))) The problem being, I do need the absolute value of the data from those cells, but not the error code because it mucks/stops up other formula: =MATCH(MAX(B52:AD52),B52:AD52,0) The cell references may differ, but the formula is the same for all<< Does anyone know of a way to get the second formula to change #VALUE! for an empty cell, as the answer, when the #VALUE! error is generated, but transfer all numbers (where there are any) in tact!?? Thanks in advance, Ted. |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Problem - interrupted by #VALUE! in other cells!?
Hi Ted
Then I think a simple extra IF on that first calculation will resolve the problem. =IF(G23="","",IF(OR(G23<G$45,G23G$46),"",IF(G$48 0,(G23-G$45)/G$47,(G$46-G23)/G$47))) If this does solve the issue, I can only apologise that we have taken so long to get here. Regards Roger Govier Ted wrote: Hi Roger, here is a list of what is in each cell respectively: For the formula =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) G23 - data G45 mean G46 standard deviation G47 1st range G48 2nd range So as you can seen because of the ¦,(G23-G$45)/G$47,.. part, when there is no data in G23, and excel goes to perform the division between mean/1st range, it cant do it and generates a #VALUE/0! Error. The data or/and error codes are then picked up by this formula in cell H23:25 etc =IF(N(H23)=0,"",ROUND(H23*G$48,3)) the above gets rid of any zeros and replaces them with empty cells/blank spaces in cell I23. The data generated by that formula is then transferred to a different part of the spreadsheet, using the new formula of =IF(ISERROR(ABS(I23)),"",ABS(I23)) {the one I was having trouble with} All data is then arranged in a line, so the greatest value can be highlighted, using this formula =MATCH(MAX(A50:Z50),A50:Z50,0) This last formula is where the #VALUE/0! became a problem, because it classes text as the greatest value (or more specifically, #DIV/0! and #VALUE/0!). Meaning that by removing anything that isnt a number becomes a necessity, otherwise you are forced to manually remove them from the arranged results. The IF(ISERROR is now stopping the text/error codes from progressing as far as the final tables, so to speak; resulting in =, what at least appears to be, success! What say you?? Ted. "Roger Govier" wrote: Hi Ted I'm glad you have got it worked out to your satisfaction. However, I still believe that intrinsically, all errors should be trapped at root. Allowing them to pervade through a series of further calculations before finally trapping them, COULD result in erroneous conclusions. You have still not said what these earlier formulae are, other than "means, SD's and the like",and I am sure it would be possible to trap errors at this level, which MAY have a bearing upon later outcomes. Still, its your call. If you are based in the States Ted, enjoy the remainder of Thanksgiving. Regards Roger Govier Ted wrote: Hi Roger, just to let you know, I have worked it out by using: =IF(ISERROR(ABS(A50)),"",ABS(A50)) thank you for your help though $;-) thanks again, Ted. "Roger Govier" wrote: Hi Ted so to reduce the possibility of there being an effect on other formula, the favored place for a preventative formula to be added is in the =(ABS(xxx...) I can't agree with you there. I think you need to ensure that any errors are trapped at their earliest possible occurrence, not way down the line through other formulae. You need to look at the formulae that give rise to the values in G23 etc. and trap there for the existence of sufficient values for the formula to be evaluated without giving rise to the #DIV/0 error. Post examples of the formulae that give rise to the values in the cells that I requested in my previous posting, then I (and/or others) can help you fix that problem. Regards Roger Govier Ted wrote: Hi Roger, G23 = data returned by other formula, the rest of the cells are means, standard deviations and ranges. The reason why the #VALUE/0! is returned, I assume at least, is because at times there is only one entry in the dataset - part of the formula =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) is looking at means and things like that - things that are dependant on there being more than one entry in the dataset (hence not being able to divide by 0). the best place for something to be added to remove the #VALUE/0! error code is in either =ROUND(H23*G$48,3) (the additional bit you say on this formula was where I had tried to get around the above problem) or in the =(ABS(Q23)) formula. the final formula is affected because it is looking for the highest score, which it would appear is the #VALUE/0! error code! so to reduce the possibility of there being an effect on other formula, the favored place for a preventative formula to be added is in the =(ABS(xxx...) formula scenes its only function is to show the content of another cell/ set of cells. If there is a way, I just want to get excel to detect anything other than numbers, and transfer it to a blank or to a zero??? Thanks for your help $;-D I will be offline for the next 2hrs, but will be back online for a few hours then - speak soon and thanks again. Ted. "Roger Govier" wrote: Hi Ted I fully understand about the confidentiality of data. I am just concerned to know the contents of various cells. You posted =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G2 3-G$45)/G$47,(G$46-G23)/G$47)) as giving a # VALUE error in any data is missing from the cells. I replied with a suggestion of =IF(OR(G23<G$45,G23G$46,G$47<1),"",IF(G$48 0,(G23-G$45)/G$47,(G$46-G23)/G$47)) which you say does not sort the problem (it didn't return an error for me with the test data I made up). Can you tell me then, what is in G23, G45, G46, G48 and G47. Also, you said that The below formula is then used to show the absolute value of that answer in another set of cells =IF(N(CO23)=0,"",(ABS(CO23))) from this I deduce that the formula above is in cell CO23, is this correct? and you say the error code because it mucks/stops up other formula: =MATCH(MAX(B52:AD52),B52:AD52,0) so I can't see why CO23 would affect this last formula which is only looking at row 52. Can you clarify? Regards Roger Govier Ted wrote: Hi Roger, thanks for getting back to me. Unfortunately I am very restricted in what I can send to you, as the database is part of a larger project, being conducted in a social science department of a University; where unfortunate, and very ridged guidelines apply. To make things more difficult still, the database contains actual subject data and information for me to delete this out, would then limit what can be seen, in terms of what has to be done to make the database perform as intended etc. Thank you for the offer though, it is appreciated. I apologise for the unusual situation, and hope there is a way you can still offer advice. With the term all I need being used very loosely; all I need really speaking is a formula that tells excel to show the absolute value of a cell (e.g. content of A1 displayed in A20). I need it to change any values that are anything but a number to a blank or a zero (blank is preferable); and need the formula to be encompassed in this formula: =ABS(A1) Thanks Roger, and sorry again for not being able to make things easier. Kind regards and speak soon, Ted. "Roger Govier" wrote: Hi Ted As I said, send me a copy. It is easier than trying to describe where all the potential pitfalls lie. Regards Roger Govier Ted wrote: Hi Roger, sorry for the delay - yeah, its not the formula that you sent that causes a problem or anything, its that the one formula (the original/initial formula that these others get the data from) can generate the #VALUE/0! error because it is dividing the answer of previous sums. If there is no data in one of those cells, then it hits a problem because it cant divide 0 by 0 etc; so gives the #VALUE/0! error message. Then, when when I ask for the ABS value to be displayed in a final set of cells, it carries the #VALUE/0! error with it (because its the content of its dependant cell). So, what I was hoping to do, is add something to the last or one from last formula to 'weed-out'/remove the error text/values, and replace them with nothing. This means that I need a formula that basically says: original formula " =ABS(DM23) " Plus 'IF answer = "#VALUE/0!" THEN give answer "" ' {where "" means blank space/empty cell etc} Any idea how I can do tis please??? Ted. "Roger Govier" wrote: Hi Ted I don't get any error with this formula. With 1 in H27 and Null in G48 from another formula, it returns 0. Send me a copy of your sheet directly and I will take a look for you. Remove NOSPAM from my address to send direct. Regards Roger Govier Ted wrote: Hi, no sorry, that doesnt work - I think it needs to be inc in the =IF(N(H27)=0,"",ROUND(H27*G$48,3)) formula as well, so that it is not affected by other formula, other than the absoult value one - any suggestions?? Thanks again, Ted. "Roger Govier" wrote: Hi Ted Perhaps =IF(OR(G23<G$45,G23G$46,G$47<1),"",IF (G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) Regards Roger Govier Ted wrote: Hi, The below formula =IF(OR(G23<G$45,G23G$46),"",IF(G$48 0,(G23-G$45)/G$47,(G$46-G23)/G$47)) is used to generate data. If there is only one data in the set, then it returns a #VALUE! Error message (as expected) The below formula is then used to show the absolute value of that answer in another set of cells =IF(N(CO23)=0,"",(ABS(CO23))) The problem being, I do need the absolute value of the data from those cells, but not the error code because it mucks/stops up other formula: =MATCH(MAX(B52:AD52),B52:AD52,0) The cell references may differ, but the formula is the same for all<< Does anyone know of a way to get the second formula to change #VALUE! for an empty cell, as the answer, when the #VALUE! error is generated, but transfer all numbers (where there are any) in tact!?? Thanks in advance, Ted. |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Problem - interrupted by #VALUE! in other cells!?
Hi Roger, thanks for the help It does get rid of some of the errors, but
because they are generated by there only being one entry in a dataset, at times, then an error code still occurs (but only in the cell that has the data saying that there is nothing to divide it by {thats expected though}. Thanks for your help and time. Kind regards, Ted "Roger Govier" wrote: Hi Ted Then I think a simple extra IF on that first calculation will resolve the problem. =IF(G23="","",IF(OR(G23<G$45,G23G$46),"",IF(G$48 0,(G23-G$45)/G$47,(G$46-G23)/G$47))) If this does solve the issue, I can only apologise that we have taken so long to get here. Regards Roger Govier Ted wrote: Hi Roger, here is a list of what is in each cell respectively: For the formula =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) G23 - data G45 mean G46 standard deviation G47 1st range G48 2nd range So as you can seen because of the ¦,(G23-G$45)/G$47,.. part, when there is no data in G23, and excel goes to perform the division between mean/1st range, it cant do it and generates a #VALUE/0! Error. The data or/and error codes are then picked up by this formula in cell H23:25 etc =IF(N(H23)=0,"",ROUND(H23*G$48,3)) the above gets rid of any zeros and replaces them with empty cells/blank spaces in cell I23. The data generated by that formula is then transferred to a different part of the spreadsheet, using the new formula of =IF(ISERROR(ABS(I23)),"",ABS(I23)) {the one I was having trouble with} All data is then arranged in a line, so the greatest value can be highlighted, using this formula =MATCH(MAX(A50:Z50),A50:Z50,0) This last formula is where the #VALUE/0! became a problem, because it classes text as the greatest value (or more specifically, #DIV/0! and #VALUE/0!). Meaning that by removing anything that isnt a number becomes a necessity, otherwise you are forced to manually remove them from the arranged results. The IF(ISERROR is now stopping the text/error codes from progressing as far as the final tables, so to speak; resulting in =, what at least appears to be, success! What say you?? Ted. "Roger Govier" wrote: Hi Ted I'm glad you have got it worked out to your satisfaction. However, I still believe that intrinsically, all errors should be trapped at root. Allowing them to pervade through a series of further calculations before finally trapping them, COULD result in erroneous conclusions. You have still not said what these earlier formulae are, other than "means, SD's and the like",and I am sure it would be possible to trap errors at this level, which MAY have a bearing upon later outcomes. Still, its your call. If you are based in the States Ted, enjoy the remainder of Thanksgiving. Regards Roger Govier Ted wrote: Hi Roger, just to let you know, I have worked it out by using: =IF(ISERROR(ABS(A50)),"",ABS(A50)) thank you for your help though $;-) thanks again, Ted. "Roger Govier" wrote: Hi Ted so to reduce the possibility of there being an effect on other formula, the favored place for a preventative formula to be added is in the =(ABS(xxx...) I can't agree with you there. I think you need to ensure that any errors are trapped at their earliest possible occurrence, not way down the line through other formulae. You need to look at the formulae that give rise to the values in G23 etc. and trap there for the existence of sufficient values for the formula to be evaluated without giving rise to the #DIV/0 error. Post examples of the formulae that give rise to the values in the cells that I requested in my previous posting, then I (and/or others) can help you fix that problem. Regards Roger Govier Ted wrote: Hi Roger, G23 = data returned by other formula, the rest of the cells are means, standard deviations and ranges. The reason why the #VALUE/0! is returned, I assume at least, is because at times there is only one entry in the dataset - part of the formula =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) is looking at means and things like that - things that are dependant on there being more than one entry in the dataset (hence not being able to divide by 0). the best place for something to be added to remove the #VALUE/0! error code is in either =ROUND(H23*G$48,3) (the additional bit you say on this formula was where I had tried to get around the above problem) or in the =(ABS(Q23)) formula. the final formula is affected because it is looking for the highest score, which it would appear is the #VALUE/0! error code! so to reduce the possibility of there being an effect on other formula, the favored place for a preventative formula to be added is in the =(ABS(xxx...) formula scenes its only function is to show the content of another cell/ set of cells. If there is a way, I just want to get excel to detect anything other than numbers, and transfer it to a blank or to a zero??? Thanks for your help $;-D I will be offline for the next 2hrs, but will be back online for a few hours then - speak soon and thanks again. Ted. "Roger Govier" wrote: Hi Ted I fully understand about the confidentiality of data. I am just concerned to know the contents of various cells. You posted =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G2 3-G$45)/G$47,(G$46-G23)/G$47)) as giving a # VALUE error in any data is missing from the cells. I replied with a suggestion of =IF(OR(G23<G$45,G23G$46,G$47<1),"",IF(G$48 0,(G23-G$45)/G$47,(G$46-G23)/G$47)) which you say does not sort the problem (it didn't return an error for me with the test data I made up). Can you tell me then, what is in G23, G45, G46, G48 and G47. Also, you said that The below formula is then used to show the absolute value of that answer in another set of cells =IF(N(CO23)=0,"",(ABS(CO23))) from this I deduce that the formula above is in cell CO23, is this correct? and you say the error code because it mucks/stops up other formula: =MATCH(MAX(B52:AD52),B52:AD52,0) so I can't see why CO23 would affect this last formula which is only looking at row 52. Can you clarify? Regards Roger Govier Ted wrote: Hi Roger, thanks for getting back to me. Unfortunately I am very restricted in what I can send to you, as the database is part of a larger project, being conducted in a social science department of a University; where unfortunate, and very ridged guidelines apply. To make things more difficult still, the database contains actual subject data and information for me to delete this out, would then limit what can be seen, in terms of what has to be done to make the database perform as intended etc. Thank you for the offer though, it is appreciated. I apologise for the unusual situation, and hope there is a way you can still offer advice. With the term all I need being used very loosely; all I need really speaking is a formula that tells excel to show the absolute value of a cell (e.g. content of A1 displayed in A20). I need it to change any values that are anything but a number to a blank or a zero (blank is preferable); and need the formula to be encompassed in this formula: =ABS(A1) Thanks Roger, and sorry again for not being able to make things easier. Kind regards and speak soon, Ted. "Roger Govier" wrote: Hi Ted As I said, send me a copy. It is easier than trying to describe where all the potential pitfalls lie. Regards Roger Govier Ted wrote: Hi Roger, sorry for the delay - yeah, its not the formula that you sent that causes a problem or anything, its that the one formula (the original/initial formula that these others get the data from) can generate the #VALUE/0! error because it is dividing the answer of previous sums. If there is no data in one of those cells, then it hits a problem because it cant divide 0 by 0 etc; so gives the #VALUE/0! error message. Then, when when I ask for the ABS value to be displayed in a final set of cells, it carries the #VALUE/0! error with it (because its the content of its dependant cell). So, what I was hoping to do, is add something to the last or one from last formula to 'weed-out'/remove the error text/values, and replace them with nothing. This means that I need a formula that basically says: original formula " =ABS(DM23) " Plus 'IF answer = "#VALUE/0!" THEN give answer "" ' {where "" means blank space/empty cell etc} Any idea how I can do tis please??? Ted. "Roger Govier" wrote: Hi Ted I don't get any error with this formula. With 1 in H27 and Null in G48 from another formula, it returns 0. Send me a copy of your sheet directly and I will take a look for you. Remove NOSPAM from my address to send direct. Regards Roger Govier |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Problem - interrupted by #VALUE! in other cells!?
Hi Ted
Then test the range of the dataset with a COUNT or COUNTIF function and if the result is less than 1, don't carry out the calculation. =IF(COUNT(A1:A10)<1,"",your_calculation) Regards Roger Govier Ted wrote: Hi Roger, thanks for the help It does get rid of some of the errors, but because they are generated by there only being one entry in a dataset, at times, then an error code still occurs (but only in the cell that has the data saying that there is nothing to divide it by {thats expected though}. Thanks for your help and time. Kind regards, Ted "Roger Govier" wrote: Hi Ted Then I think a simple extra IF on that first calculation will resolve the problem. =IF(G23="","",IF(OR(G23<G$45,G23G$46),"",IF(G$4 80,(G23-G$45)/G$47,(G$46-G23)/G$47))) If this does solve the issue, I can only apologise that we have taken so long to get here. Regards Roger Govier Ted wrote: Hi Roger, here is a list of what is in each cell respectively: For the formula =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47)) G23 - data G45 mean G46 standard deviation G47 1st range G48 2nd range So as you can seen because of the ¦,(G23-G$45)/G$47,.. part, when there is no data in G23, and excel goes to perform the division between mean/1st range, it cant do it and generates a #VALUE/0! Error. The data or/and error codes are then picked up by this formula in cell H23:25 etc =IF(N(H23)=0,"",ROUND(H23*G$48,3)) the above gets rid of any zeros and replaces them with empty cells/blank spaces in cell I23. The data generated by that formula is then transferred to a different part of the spreadsheet, using the new formula of =IF(ISERROR(ABS(I23)),"",ABS(I23)) {the one I was having trouble with} All data is then arranged in a line, so the greatest value can be highlighted, using this formula =MATCH(MAX(A50:Z50),A50:Z50,0) This last formula is where the #VALUE/0! became a problem, because it classes text as the greatest value (or more specifically, #DIV/0! and #VALUE/0!). Meaning that by removing anything that isnt a number becomes a necessity, otherwise you are forced to manually remove them from the arranged results. The IF(ISERROR is now stopping the text/error codes from progressing as far as the final tables, so to speak; resulting in =, what at least appears to be, success! What say you?? Ted. "Roger Govier" wrote: Hi Ted I'm glad you have got it worked out to your satisfaction. However, I still believe that intrinsically, all errors should be trapped at root. Allowing them to pervade through a series of further calculations before finally trapping them, COULD result in erroneous conclusions. You have still not said what these earlier formulae are, other than "means, SD's and the like",and I am sure it would be possible to trap errors at this level, which MAY have a bearing upon later outcomes. Still, its your call. If you are based in the States Ted, enjoy the remainder of Thanksgiving. Regards Roger Govier Ted wrote: Hi Roger, just to let you know, I have worked it out by using: =IF(ISERROR(ABS(A50)),"",ABS(A50)) thank you for your help though $;-) thanks again, Ted. "Roger Govier" wrote: Hi Ted so to reduce the possibility of there being an effect on other formula, the favored place for a preventative formula to be added is in the =(ABS(xxx...) I can't agree with you there. I think you need to ensure that any errors are trapped at their earliest possible occurrence, not way down the line through other formulae. You need to look at the formulae that give rise to the values in G23 etc. and trap there for the existence of sufficient values for the formula to be evaluated without giving rise to the #DIV/0 error. Post examples of the formulae that give rise to the values in the cells that I requested in my previous posting, then I (and/or others) can help you fix that problem. Regards Roger Govier Ted wrote: Hi Roger, G23 = data returned by other formula, the rest of the cells are means, standard deviations and ranges. The reason why the #VALUE/0! is returned, I assume at least, is because at times there is only one entry in the dataset - part of the formula =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G 23-G$45)/G$47,(G$46-G23)/G$47)) is looking at means and things like that - things that are dependant on there being more than one entry in the dataset (hence not being able to divide by 0). the best place for something to be added to remove the #VALUE/0! error code is in either =ROUND(H23*G$48,3) (the additional bit you say on this formula was where I had tried to get around the above problem) or in the =(ABS(Q23)) formula. the final formula is affected because it is looking for the highest score, which it would appear is the #VALUE/0! error code! so to reduce the possibility of there being an effect on other formula, the favored place for a preventative formula to be added is in the =(ABS(xxx...) formula scenes its only function is to show the content of another cell/ set of cells. If there is a way, I just want to get excel to detect anything other than numbers, and transfer it to a blank or to a zero??? Thanks for your help $;-D I will be offline for the next 2hrs, but will be back online for a few hours then - speak soon and thanks again. Ted. "Roger Govier" wrote: Hi Ted I fully understand about the confidentiality of data. I am just concerned to know the contents of various cells. You posted =IF(OR(G23<G$45,G23G$46),"",IF(G$480,(G2 3-G$45)/G$47,(G$46-G23)/G$47)) as giving a # VALUE error in any data is missing from the cells. I replied with a suggestion of =IF(OR(G23<G$45,G23G$46,G$47<1),"",IF(G$4 80,(G23-G$45)/G$47,(G$46-G23)/G$47)) which you say does not sort the problem (it didn't return an error for me with the test data I made up). Can you tell me then, what is in G23, G45, G46, G48 and G47. Also, you said that The below formula is then used to show the absolute value of that answer in another set of cells =IF(N(CO23)=0,"",(ABS(CO23))) from this I deduce that the formula above is in cell CO23, is this correct? and you say the error code because it mucks/stops up other formula: =MATCH(MAX(B52:AD52),B52:AD52,0) so I can't see why CO23 would affect this last formula which is only looking at row 52. Can you clarify? Regards Roger Govier Ted wrote: Hi Roger, thanks for getting back to me. Unfortunately I am very restricted in what I can send to you, as the database is part of a larger project, being conducted in a social science department of a University; where unfortunate, and very ridged guidelines apply. To make things more difficult still, the database contains actual subject data and information for me to delete this out, would then limit what can be seen, in terms of what has to be done to make the database perform as intended etc. Thank you for the offer though, it is appreciated. I apologise for the unusual situation, and hope there is a way you can still offer advice. With the term all I need being used very loosely; all I need really speaking is a formula that tells excel to show the absolute value of a cell (e.g. content of A1 displayed in A20). I need it to change any values that are anything but a number to a blank or a zero (blank is preferable); and need the formula to be encompassed in this formula: =ABS(A1) Thanks Roger, and sorry again for not being able to make things easier. Kind regards and speak soon, Ted. "Roger Govier" wrote: Hi Ted As I said, send me a copy. It is easier than trying to describe where all the potential pitfalls lie. Regards Roger Govier Ted wrote: Hi Roger, sorry for the delay - yeah, its not the formula that you sent that causes a problem or anything, its that the one formula (the original/initial formula that these others get the data from) can generate the #VALUE/0! error because it is dividing the answer of previous sums. If there is no data in one of those cells, then it hits a problem because it cant divide 0 by 0 etc; so gives the #VALUE/0! error message. Then, when when I ask for the ABS value to be displayed in a final set of cells, it carries the #VALUE/0! error with it (because its the content of its dependant cell). So, what I was hoping to do, is add something to the last or one from last formula to 'weed-out'/remove the error text/values, and replace them with nothing. This means that I need a formula that basically says: original formula " =ABS(DM23) " Plus 'IF answer = "#VALUE/0!" THEN give answer "" ' {where "" means blank space/empty cell etc} Any idea how I can do tis please??? Ted. "Roger Govier" wrote: Hi Ted I don't get any error with this formula. With 1 in H27 and Null in G48 from another formula, it returns 0. Send me a copy of your sheet directly and I will take a look for you. Remove NOSPAM from my address to send direct. Regards Roger Govier |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
The Excel cells are not automatically calc. existing formula. | Excel Worksheet Functions | |||
problem with Array Formula | Excel Worksheet Functions | |||
Formula works in some cells, doesn't in other | New Users to Excel | |||
trying to create an (almost) circular formula between cells and data validated cells with lists | Excel Worksheet Functions | |||
Conditional formatting on cells with a VLOOKUP formula in them | Excel Discussion (Misc queries) |