Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ok, here is the Correct equation I am having trouble with€¦ (from T
I have two main steps that I am trying to make in to one, which a
=ROUND(7/(A5-B5),5) here I want it to take divide 7 A5-B5 rounded to 5 decimal places =IF(A50.5,A5,"") here I want it to remove any answers from the above that equal 0.5 or less because its outside of the range I am looking at. Thats why I have added the A5,€¯€¯ part €“ so to replace the answer with a blank(?) I have tried many different combinations to get these two to add together, so I can run it al in one cell, rather than two sets as I currently have. Is this possible, and if so, how please?? Thanks for all earlier responses, and I am sorry for the poor equations I entered earlier, that didnt even make sense because I wrote them wrong €“ not a good start! Ted. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ok, here is the Correct equation I am having trouble with. (from T
Hi!
If you need further follow-ups keep posting them to the same thread. It's easier to follow what has taken place. Try this: =IF(ROUND(7/(A5-B5),5)0.5,A5,"") Biff "Ted" wrote in message ... I have two main steps that I am trying to make in to one, which a =ROUND(7/(A5-B5),5) here I want it to take divide 7 A5-B5 rounded to 5 decimal places =IF(A50.5,A5,"") here I want it to remove any answers from the above that equal 0.5 or less because its outside of the range I am looking at. That's why I have added the A5,"" part - so to replace the answer with a blank(?) I have tried many different combinations to get these two to add together, so I can run it al in one cell, rather than two sets as I currently have. Is this possible, and if so, how please?? Thanks for all earlier responses, and I am sorry for the poor equations I entered earlier, that didn't even make sense because I wrote them wrong - not a good start! Ted. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ok, here is the Correct equation I am having trouble with. (fr
Hi Biff, thanks for your response and your help. Unfortunately it doesnt
work though €“ it returns a #DIV/0! In to the intended cell, which stops the rest of the analysis being performed. It seems that the only way I can get the rest of the stuff to run, is if cells that have no value in (i.e. a zero) is completely blank. Is there a code that I can add that will ask it to change any zeros to blanks €“ that might work?? Any ideas or suggestions much appreciated and thank you in advance. Ted. "Biff" wrote: Hi! If you need further follow-ups keep posting them to the same thread. It's easier to follow what has taken place. Try this: =IF(ROUND(7/(A5-B5),5)0.5,A5,"") Biff "Ted" wrote in message ... I have two main steps that I am trying to make in to one, which a =ROUND(7/(A5-B5),5) here I want it to take divide 7 A5-B5 rounded to 5 decimal places =IF(A50.5,A5,"") here I want it to remove any answers from the above that equal 0.5 or less because its outside of the range I am looking at. That's why I have added the A5,"" part - so to replace the answer with a blank(?) I have tried many different combinations to get these two to add together, so I can run it al in one cell, rather than two sets as I currently have. Is this possible, and if so, how please?? Thanks for all earlier responses, and I am sorry for the poor equations I entered earlier, that didn't even make sense because I wrote them wrong - not a good start! Ted. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ok, here is the Correct equation I am having trouble with. (fr
Hi, I have just realised that the reason why its returning a 'DIV/0! is
because there wasn't an entry in one of the cells (in one of the A3-D3 cells). This raises a new problem, is there a way I can get it to ignor cells that have no numbers to be computed (e.g. if the data was A3-B3, A4-B4, A5-B5, and A3-A4 and A5-B5 have data in, but A4-B4 has no values/data in. I would need it to ignor A4 and B4, and return a blank (as in completely empty) cell in the cell where the answer is then supposed to reside - any suggestions?? also, working with Biff's earlier suggestion of: =IF(ROUND(7/(A5-B5),5)0.5,A5,"") this all works great (thanks Biff $;-) but the end cell causes a problem - the last part of ...,A5,""), for some reason alters the answer (from 6047799.858 to 36891.000). I am only guessing that it is the end part thats altering things because when I break the sum down to ROUND(7/A5-B5),0.5 it works fine, and if I change the A5 to a cell reference where I have other data in it, it displays that data ok (throwing out any data that is 0.5 and returning a blank cell in its place). So, I assume that it may be either the order in which the sums are computed or the A5 part - I think its the A5 part myself, but don't know why it does it - have you any suggestions please?? Ted. "Biff" wrote: Hi! If you need further follow-ups keep posting them to the same thread. It's easier to follow what has taken place. Try this: =IF(ROUND(7/(A5-B5),5)0.5,A5,"") Biff "Ted" wrote in message ... I have two main steps that I am trying to make in to one, which a =ROUND(7/(A5-B5),5) here I want it to take divide 7 A5-B5 rounded to 5 decimal places =IF(A50.5,A5,"") here I want it to remove any answers from the above that equal 0.5 or less because its outside of the range I am looking at. That's why I have added the A5,"" part - so to replace the answer with a blank(?) I have tried many different combinations to get these two to add together, so I can run it al in one cell, rather than two sets as I currently have. Is this possible, and if so, how please?? Thanks for all earlier responses, and I am sorry for the poor equations I entered earlier, that didn't even make sense because I wrote them wrong - not a good start! Ted. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ok, here is the Correct equation I am having trouble with. (fr
"Ted" wrote: Hi, I have just realised that the reason why its returning a 'DIV/0! is because there wasn't an entry in one of the cells (in one of the A3-D3 cells). This raises a new problem, is there a way I can get it to ignor cells that have no numbers to be computed (e.g. if the data was A3-B3, A4-B4, A5-B5, and A3-A4 and A5-B5 have data in, but A4-B4 has no values/data in. I would need it to ignor A4 and B4, and return a blank (as in completely empty) cell in the cell where the answer is then supposed to reside - any suggestions?? also, working with Biff's earlier suggestion of: =IF(ROUND(7/(A5-B5),5)0.5,A5,"") this all works great (thanks Biff $;-) but the end cell causes a problem - the last part of ...,A5,""), for some reason alters the answer (from 6047799.858 to 36891.000). I am only guessing that it is the end part thats altering things because when I break the sum down to ROUND(7/A5-B5),0.5 it works fine, and if I change the A5 to a cell reference where I have other data in it, it displays that data ok (throwing out any data that is 0.5 and returning a blank cell in its place). So, I assume that it may be either the order in which the sums are computed or the A5 part - I think its the A5 part myself, but don't know why it does it - have you any suggestions please?? Ted. "Biff" wrote: Hi! If you need further follow-ups keep posting them to the same thread. It's easier to follow what has taken place. Try this: =IF(ROUND(7/(A5-B5),5)0.5,A5,"") Biff "Ted" wrote in message ... I have two main steps that I am trying to make in to one, which a =ROUND(7/(A5-B5),5) here I want it to take divide 7 A5-B5 rounded to 5 decimal places =IF(A50.5,A5,"") here I want it to remove any answers from the above that equal 0.5 or less because its outside of the range I am looking at. That's why I have added the A5,"" part - so to replace the answer with a blank(?) I have tried many different combinations to get these two to add together, so I can run it al in one cell, rather than two sets as I currently have. Is this possible, and if so, how please?? Thanks for all earlier responses, and I am sorry for the poor equations I entered earlier, that didn't even make sense because I wrote them wrong - not a good start! Ted. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ok, here is the Correct equation I am having trouble with. (fr
I can get around it by computing
=ROUND(7/(A3-B3),5) into cell C3 then, =IF(ROUND(7/(A3-B3),5)0.5,C3,"") this allows me to achieve the intended, but is still a bit messy because it involves using a new set of cells to act as a reference (e.g. C3,C4,C5 and so on). I still have no answers for the ignoring blank cells in the initial equation as well (e.g. the A3-B3,A4-B4,A5-B5 part), so are open to suggestions for that too please?? Regards, Ted. "Ted" wrote: Hi, I have just realised that the reason why its returning a 'DIV/0! is because there wasn't an entry in one of the cells (in one of the A3-D3 cells). This raises a new problem, is there a way I can get it to ignor cells that have no numbers to be computed (e.g. if the data was A3-B3, A4-B4, A5-B5, and A3-A4 and A5-B5 have data in, but A4-B4 has no values/data in. I would need it to ignor A4 and B4, and return a blank (as in completely empty) cell in the cell where the answer is then supposed to reside - any suggestions?? also, working with Biff's earlier suggestion of: =IF(ROUND(7/(A5-B5),5)0.5,A5,"") this all works great (thanks Biff $;-) but the end cell causes a problem - the last part of ...,A5,""), for some reason alters the answer (from 6047799.858 to 36891.000). I am only guessing that it is the end part thats altering things because when I break the sum down to ROUND(7/A5-B5),0.5 it works fine, and if I change the A5 to a cell reference where I have other data in it, it displays that data ok (throwing out any data that is 0.5 and returning a blank cell in its place). So, I assume that it may be either the order in which the sums are computed or the A5 part - I think its the A5 part myself, but don't know why it does it - have you any suggestions please?? Ted. "Biff" wrote: Hi! If you need further follow-ups keep posting them to the same thread. It's easier to follow what has taken place. Try this: =IF(ROUND(7/(A5-B5),5)0.5,A5,"") Biff "Ted" wrote in message ... I have two main steps that I am trying to make in to one, which a =ROUND(7/(A5-B5),5) here I want it to take divide 7 A5-B5 rounded to 5 decimal places =IF(A50.5,A5,"") here I want it to remove any answers from the above that equal 0.5 or less because its outside of the range I am looking at. That's why I have added the A5,"" part - so to replace the answer with a blank(?) I have tried many different combinations to get these two to add together, so I can run it al in one cell, rather than two sets as I currently have. Is this possible, and if so, how please?? Thanks for all earlier responses, and I am sorry for the poor equations I entered earlier, that didn't even make sense because I wrote them wrong - not a good start! Ted. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ok, here is the Correct equation I am having trouble with. (fr
Try
=IF(OR(ISNUMBER(A5),ISNUMBER(B5),A5-B5=0),IF(ROUND(7/(A5-B5),5)0.5,A5,""),"") if I types it right, it should leave the target cell blank if there is not a number in either A5 or B5, and will also prevent a "Divide by 0" error. "Ted" wrote in message ... Hi, I have just realised that the reason why its returning a 'DIV/0! is because there wasn't an entry in one of the cells (in one of the A3-D3 cells). This raises a new problem, is there a way I can get it to ignor cells that have no numbers to be computed (e.g. if the data was A3-B3, A4-B4, A5-B5, and A3-A4 and A5-B5 have data in, but A4-B4 has no values/data in. I would need it to ignor A4 and B4, and return a blank (as in completely empty) cell in the cell where the answer is then supposed to reside - any suggestions?? also, working with Biff's earlier suggestion of: =IF(ROUND(7/(A5-B5),5)0.5,A5,"") this all works great (thanks Biff $;-) but the end cell causes a problem - the last part of ...,A5,""), for some reason alters the answer (from 6047799.858 to 36891.000). I am only guessing that it is the end part thats altering things because when I break the sum down to ROUND(7/A5-B5),0.5 it works fine, and if I change the A5 to a cell reference where I have other data in it, it displays that data ok (throwing out any data that is 0.5 and returning a blank cell in its place). So, I assume that it may be either the order in which the sums are computed or the A5 part - I think its the A5 part myself, but don't know why it does it - have you any suggestions please?? Ted. "Biff" wrote: Hi! If you need further follow-ups keep posting them to the same thread. It's easier to follow what has taken place. Try this: =IF(ROUND(7/(A5-B5),5)0.5,A5,"") Biff "Ted" wrote in message ... I have two main steps that I am trying to make in to one, which a =ROUND(7/(A5-B5),5) here I want it to take divide 7 A5-B5 rounded to 5 decimal places =IF(A50.5,A5,"") here I want it to remove any answers from the above that equal 0.5 or less because its outside of the range I am looking at. That's why I have added the A5,"" part - so to replace the answer with a blank(?) I have tried many different combinations to get these two to add together, so I can run it al in one cell, rather than two sets as I currently have. Is this possible, and if so, how please?? Thanks for all earlier responses, and I am sorry for the poor equations I entered earlier, that didn't even make sense because I wrote them wrong - not a good start! Ted. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ok, here is the Correct equation I am having trouble with. (fr
should be an AND command with A5-B5<0
=IF(AND(ISNUMBER(A5),ISNUMBER(B5),A5-B5<0),IF(ROUND(7/(A5-B5),5)0.5,A5,""),"") it might be better if I edited myself a bit more thouroughly. "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message ... Try =IF(OR(ISNUMBER(A5),ISNUMBER(B5),A5-B5=0),IF(ROUND(7/(A5-B5),5)0.5,A5,""),"") if I types it right, it should leave the target cell blank if there is not a number in either A5 or B5, and will also prevent a "Divide by 0" error. "Ted" wrote in message ... Hi, I have just realised that the reason why its returning a 'DIV/0! is because there wasn't an entry in one of the cells (in one of the A3-D3 cells). This raises a new problem, is there a way I can get it to ignor cells that have no numbers to be computed (e.g. if the data was A3-B3, A4-B4, A5-B5, and A3-A4 and A5-B5 have data in, but A4-B4 has no values/data in. I would need it to ignor A4 and B4, and return a blank (as in completely empty) cell in the cell where the answer is then supposed to reside - any suggestions?? also, working with Biff's earlier suggestion of: =IF(ROUND(7/(A5-B5),5)0.5,A5,"") this all works great (thanks Biff $;-) but the end cell causes a problem - the last part of ...,A5,""), for some reason alters the answer (from 6047799.858 to 36891.000). I am only guessing that it is the end part thats altering things because when I break the sum down to ROUND(7/A5-B5),0.5 it works fine, and if I change the A5 to a cell reference where I have other data in it, it displays that data ok (throwing out any data that is 0.5 and returning a blank cell in its place). So, I assume that it may be either the order in which the sums are computed or the A5 part - I think its the A5 part myself, but don't know why it does it - have you any suggestions please?? Ted. "Biff" wrote: Hi! If you need further follow-ups keep posting them to the same thread. It's easier to follow what has taken place. Try this: =IF(ROUND(7/(A5-B5),5)0.5,A5,"") Biff "Ted" wrote in message ... I have two main steps that I am trying to make in to one, which a =ROUND(7/(A5-B5),5) here I want it to take divide 7 A5-B5 rounded to 5 decimal places =IF(A50.5,A5,"") here I want it to remove any answers from the above that equal 0.5 or less because its outside of the range I am looking at. That's why I have added the A5,"" part - so to replace the answer with a blank(?) I have tried many different combinations to get these two to add together, so I can run it al in one cell, rather than two sets as I currently have. Is this possible, and if so, how please?? Thanks for all earlier responses, and I am sorry for the poor equations I entered earlier, that didn't even make sense because I wrote them wrong - not a good start! Ted. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ok, here is the Correct equation I am having trouble with. (fr
Hi, thanks for your help, but it seems that it alters the answer! when the
answer should be 6047799.858 it comes out as 36891.000 any ideas?? thanks, Ted. "Adam Kroger @hotmail.com" wrote: Try =IF(OR(ISNUMBER(A5),ISNUMBER(B5),A5-B5=0),IF(ROUND(7/(A5-B5),5)0.5,A5,""),"") if I types it right, it should leave the target cell blank if there is not a number in either A5 or B5, and will also prevent a "Divide by 0" error. "Ted" wrote in message ... Hi, I have just realised that the reason why its returning a 'DIV/0! is because there wasn't an entry in one of the cells (in one of the A3-D3 cells). This raises a new problem, is there a way I can get it to ignor cells that have no numbers to be computed (e.g. if the data was A3-B3, A4-B4, A5-B5, and A3-A4 and A5-B5 have data in, but A4-B4 has no values/data in. I would need it to ignor A4 and B4, and return a blank (as in completely empty) cell in the cell where the answer is then supposed to reside - any suggestions?? also, working with Biff's earlier suggestion of: =IF(ROUND(7/(A5-B5),5)0.5,A5,"") this all works great (thanks Biff $;-) but the end cell causes a problem - the last part of ...,A5,""), for some reason alters the answer (from 6047799.858 to 36891.000). I am only guessing that it is the end part thats altering things because when I break the sum down to ROUND(7/A5-B5),0.5 it works fine, and if I change the A5 to a cell reference where I have other data in it, it displays that data ok (throwing out any data that is 0.5 and returning a blank cell in its place). So, I assume that it may be either the order in which the sums are computed or the A5 part - I think its the A5 part myself, but don't know why it does it - have you any suggestions please?? Ted. "Biff" wrote: Hi! If you need further follow-ups keep posting them to the same thread. It's easier to follow what has taken place. Try this: =IF(ROUND(7/(A5-B5),5)0.5,A5,"") Biff "Ted" wrote in message ... I have two main steps that I am trying to make in to one, which a =ROUND(7/(A5-B5),5) here I want it to take divide 7 A5-B5 rounded to 5 decimal places =IF(A50.5,A5,"") here I want it to remove any answers from the above that equal 0.5 or less because its outside of the range I am looking at. That's why I have added the A5,"" part - so to replace the answer with a blank(?) I have tried many different combinations to get these two to add together, so I can run it al in one cell, rather than two sets as I currently have. Is this possible, and if so, how please?? Thanks for all earlier responses, and I am sorry for the poor equations I entered earlier, that didn't even make sense because I wrote them wrong - not a good start! Ted. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ok, here is the Correct equation I am having trouble with. (fr
Hi Adam, thanks - I added the change, but it still comes out with the lower
amount as the answer - so I guess it worked even without the AND, but assume that the part that is changing the amount is something different - I dont think its simply putting the 7/ after the summing either because 7/A5-B5 = 604799.8586 and A5-B5/7 = 31620.85715, but the answer that we are generating is 36891.000001! any ideas?? Ted. "Adam Kroger @hotmail.com" wrote: should be an AND command with A5-B5<0 =IF(AND(ISNUMBER(A5),ISNUMBER(B5),A5-B5<0),IF(ROUND(7/(A5-B5),5)0.5,A5,""),"") it might be better if I edited myself a bit more thouroughly. "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message ... Try =IF(OR(ISNUMBER(A5),ISNUMBER(B5),A5-B5=0),IF(ROUND(7/(A5-B5),5)0.5,A5,""),"") if I types it right, it should leave the target cell blank if there is not a number in either A5 or B5, and will also prevent a "Divide by 0" error. "Ted" wrote in message ... Hi, I have just realised that the reason why its returning a 'DIV/0! is because there wasn't an entry in one of the cells (in one of the A3-D3 cells). This raises a new problem, is there a way I can get it to ignor cells that have no numbers to be computed (e.g. if the data was A3-B3, A4-B4, A5-B5, and A3-A4 and A5-B5 have data in, but A4-B4 has no values/data in. I would need it to ignor A4 and B4, and return a blank (as in completely empty) cell in the cell where the answer is then supposed to reside - any suggestions?? also, working with Biff's earlier suggestion of: =IF(ROUND(7/(A5-B5),5)0.5,A5,"") this all works great (thanks Biff $;-) but the end cell causes a problem - the last part of ...,A5,""), for some reason alters the answer (from 6047799.858 to 36891.000). I am only guessing that it is the end part thats altering things because when I break the sum down to ROUND(7/A5-B5),0.5 it works fine, and if I change the A5 to a cell reference where I have other data in it, it displays that data ok (throwing out any data that is 0.5 and returning a blank cell in its place). So, I assume that it may be either the order in which the sums are computed or the A5 part - I think its the A5 part myself, but don't know why it does it - have you any suggestions please?? Ted. "Biff" wrote: Hi! If you need further follow-ups keep posting them to the same thread. It's easier to follow what has taken place. Try this: =IF(ROUND(7/(A5-B5),5)0.5,A5,"") Biff "Ted" wrote in message ... I have two main steps that I am trying to make in to one, which a =ROUND(7/(A5-B5),5) here I want it to take divide 7 A5-B5 rounded to 5 decimal places =IF(A50.5,A5,"") here I want it to remove any answers from the above that equal 0.5 or less because its outside of the range I am looking at. That's why I have added the A5,"" part - so to replace the answer with a blank(?) I have tried many different combinations to get these two to add together, so I can run it al in one cell, rather than two sets as I currently have. Is this possible, and if so, how please?? Thanks for all earlier responses, and I am sorry for the poor equations I entered earlier, that didn't even make sense because I wrote them wrong - not a good start! Ted. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ok, here is the Correct equation I am having trouble with. (fr
Hmmmm, I'm not sure why the calculation would change. The "IF(AND(...))" as
I understand them, shouldn't impact the actual computation inside it. Is it (the computation inside) 100% identical to what it was before? "Ted" wrote in message ... Hi Adam, thanks - I added the change, but it still comes out with the lower amount as the answer - so I guess it worked even without the AND, but assume that the part that is changing the amount is something different - I dont think its simply putting the 7/ after the summing either because 7/A5-B5 = 604799.8586 and A5-B5/7 = 31620.85715, but the answer that we are generating is 36891.000001! any ideas?? Ted. "Adam Kroger @hotmail.com" wrote: should be an AND command with A5-B5<0 =IF(AND(ISNUMBER(A5),ISNUMBER(B5),A5-B5<0),IF(ROUND(7/(A5-B5),5)0.5,A5,""),"") it might be better if I edited myself a bit more thouroughly. "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message ... Try =IF(OR(ISNUMBER(A5),ISNUMBER(B5),A5-B5=0),IF(ROUND(7/(A5-B5),5)0.5,A5,""),"") if I types it right, it should leave the target cell blank if there is not a number in either A5 or B5, and will also prevent a "Divide by 0" error. "Ted" wrote in message ... Hi, I have just realised that the reason why its returning a 'DIV/0! is because there wasn't an entry in one of the cells (in one of the A3-D3 cells). This raises a new problem, is there a way I can get it to ignor cells that have no numbers to be computed (e.g. if the data was A3-B3, A4-B4, A5-B5, and A3-A4 and A5-B5 have data in, but A4-B4 has no values/data in. I would need it to ignor A4 and B4, and return a blank (as in completely empty) cell in the cell where the answer is then supposed to reside - any suggestions?? also, working with Biff's earlier suggestion of: =IF(ROUND(7/(A5-B5),5)0.5,A5,"") this all works great (thanks Biff $;-) but the end cell causes a problem - the last part of ...,A5,""), for some reason alters the answer (from 6047799.858 to 36891.000). I am only guessing that it is the end part thats altering things because when I break the sum down to ROUND(7/A5-B5),0.5 it works fine, and if I change the A5 to a cell reference where I have other data in it, it displays that data ok (throwing out any data that is 0.5 and returning a blank cell in its place). So, I assume that it may be either the order in which the sums are computed or the A5 part - I think its the A5 part myself, but don't know why it does it - have you any suggestions please?? Ted. "Biff" wrote: Hi! If you need further follow-ups keep posting them to the same thread. It's easier to follow what has taken place. Try this: =IF(ROUND(7/(A5-B5),5)0.5,A5,"") Biff "Ted" wrote in message ... I have two main steps that I am trying to make in to one, which a =ROUND(7/(A5-B5),5) here I want it to take divide 7 A5-B5 rounded to 5 decimal places =IF(A50.5,A5,"") here I want it to remove any answers from the above that equal 0.5 or less because its outside of the range I am looking at. That's why I have added the A5,"" part - so to replace the answer with a blank(?) I have tried many different combinations to get these two to add together, so I can run it al in one cell, rather than two sets as I currently have. Is this possible, and if so, how please?? Thanks for all earlier responses, and I am sorry for the poor equations I entered earlier, that didn't even make sense because I wrote them wrong - not a good start! Ted. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ok, here is the Correct equation I am having trouble with. (fr
Hi, yep, all is the same and if I cut the additions out to just leave the
7/(... part, it comes up with the correct answer! an odd one hey! Ted. "Adam Kroger @hotmail.com" wrote: Hmmmm, I'm not sure why the calculation would change. The "IF(AND(...))" as I understand them, shouldn't impact the actual computation inside it. Is it (the computation inside) 100% identical to what it was before? "Ted" wrote in message ... Hi Adam, thanks - I added the change, but it still comes out with the lower amount as the answer - so I guess it worked even without the AND, but assume that the part that is changing the amount is something different - I dont think its simply putting the 7/ after the summing either because 7/A5-B5 = 604799.8586 and A5-B5/7 = 31620.85715, but the answer that we are generating is 36891.000001! any ideas?? Ted. "Adam Kroger @hotmail.com" wrote: should be an AND command with A5-B5<0 =IF(AND(ISNUMBER(A5),ISNUMBER(B5),A5-B5<0),IF(ROUND(7/(A5-B5),5)0.5,A5,""),"") it might be better if I edited myself a bit more thouroughly. "Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message ... Try =IF(OR(ISNUMBER(A5),ISNUMBER(B5),A5-B5=0),IF(ROUND(7/(A5-B5),5)0.5,A5,""),"") if I types it right, it should leave the target cell blank if there is not a number in either A5 or B5, and will also prevent a "Divide by 0" error. "Ted" wrote in message ... Hi, I have just realised that the reason why its returning a 'DIV/0! is because there wasn't an entry in one of the cells (in one of the A3-D3 cells). This raises a new problem, is there a way I can get it to ignor cells that have no numbers to be computed (e.g. if the data was A3-B3, A4-B4, A5-B5, and A3-A4 and A5-B5 have data in, but A4-B4 has no values/data in. I would need it to ignor A4 and B4, and return a blank (as in completely empty) cell in the cell where the answer is then supposed to reside - any suggestions?? also, working with Biff's earlier suggestion of: =IF(ROUND(7/(A5-B5),5)0.5,A5,"") this all works great (thanks Biff $;-) but the end cell causes a problem - the last part of ...,A5,""), for some reason alters the answer (from 6047799.858 to 36891.000). I am only guessing that it is the end part thats altering things because when I break the sum down to ROUND(7/A5-B5),0.5 it works fine, and if I change the A5 to a cell reference where I have other data in it, it displays that data ok (throwing out any data that is 0.5 and returning a blank cell in its place). So, I assume that it may be either the order in which the sums are computed or the A5 part - I think its the A5 part myself, but don't know why it does it - have you any suggestions please?? Ted. "Biff" wrote: Hi! If you need further follow-ups keep posting them to the same thread. It's easier to follow what has taken place. Try this: =IF(ROUND(7/(A5-B5),5)0.5,A5,"") Biff "Ted" wrote in message ... I have two main steps that I am trying to make in to one, which a =ROUND(7/(A5-B5),5) here I want it to take divide 7 A5-B5 rounded to 5 decimal places =IF(A50.5,A5,"") here I want it to remove any answers from the above that equal 0.5 or less because its outside of the range I am looking at. That's why I have added the A5,"" part - so to replace the answer with a blank(?) I have tried many different combinations to get these two to add together, so I can run it al in one cell, rather than two sets as I currently have. Is this possible, and if so, how please?? Thanks for all earlier responses, and I am sorry for the poor equations I entered earlier, that didn't even make sense because I wrote them wrong - not a good start! Ted. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
To find equation for two sets of non contiguous data | Charts and Charting in Excel | |||
Equation Editor- problem when editing an equation | Excel Discussion (Misc queries) | |||
Need Help with #N/A in equation | Excel Discussion (Misc queries) | |||
Averaging/Rounding Equation problem | Excel Discussion (Misc queries) | |||
Logarithmic Trendline Equation | Charts and Charting in Excel |