Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ted
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ted
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ted
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ted
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ted
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Adam Kroger
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Adam Kroger
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ted
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ted
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Adam Kroger
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ted
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
To find equation for two sets of non contiguous data vijaya Charts and Charting in Excel 2 November 7th 05 04:46 AM
Equation Editor- problem when editing an equation Gaby L. Excel Discussion (Misc queries) 0 September 27th 05 09:24 PM
Need Help with #N/A in equation mkerstei Excel Discussion (Misc queries) 7 July 29th 05 09:03 PM
Averaging/Rounding Equation problem Hansel Excel Discussion (Misc queries) 4 June 28th 05 08:45 PM
Logarithmic Trendline Equation Phil Hageman Charts and Charting in Excel 2 January 13th 05 12:55 PM


All times are GMT +1. The time now is 02:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"