Ok, here is the Correct equation I am having trouble with… (from T

T

Ted

I have two main steps that I am trying to make in to one, which are:

=ROUND(7/(A5-B5),5)
here I want it to take divide 7 A5-B5 rounded to 5 decimal places

=IF(A5>0.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.
 
B

Biff

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
 
T

Ted

Hi Biff, thanks for your response and your help. Unfortunately it doesn’t
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.
 
T

Ted

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.
 
T

Ted

Ted said:
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.
Click to expand...
 
T

Ted

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.
 
A

Adam Kroger

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.
 
A

Adam Kroger

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.
 
T

Ted

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.
 
T

Ted

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.
 
A

Adam Kroger

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?
 
T

Ted

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.
 
Top