Logical test for integer or non- integer

**MHRW** · 08-01-2011, 08:54 PM

I am trying to calculate the relative percent difference (rpd) between the values in two cells where one or both the values may be input as "<0.1" (as an example). The rpd formula is:

=ABS(x-y/((x+y)/2))

Where the data values could be any of the following four formats:

x , y
1 , 2
1 , <0.2
<0.2 , 2
<0.1 , <0.1

If both values are in the "<0.1" format I need "BBL" to be displayed, If one is "<0.1" I need the formula to see that cell as "0.1" (I can have another cell with "0.1" that it can reference).

I think all I need is a nested IF statement where the logical test decides if each cell is an integer or not.

Closest I've got (which doesn't work) is:

=IF((AND(A1>0,B1>0)),ABS((A1-B1)/((A1+B1)),IF(A1<0,ABS((C1-B1)/((C1+B1)/2)),IF(B1>0,ABS((A1-C1)/((A1+C1)/2)),(IF((AND(A1<0,B1<0)),BBL,"Error")))))

Where C1 is "0.1"

Any help would be awesome!

**Alf** · 08-02-2011, 01:42 PM

Perhaps a formula like this?

=IF(AND(A1<0.1,B1<0.1),"BBL",IF(AND(A1<0.1,B1>0.1),ABS($C$1-B1/(($C$1+B1)/2)),IF(AND(A1>0.1,B1<0.1),ABS(A1-$C$1/((A1+$C$1)/2)),ABS(A1-B1/((A1+B1)/2)))))

This formula assumes that you have the value 0.1 in cell C1.

Question: If both A1 and B1 is equal to 0.1 do you have a special case fore that?

If you want to test for integre one way would be to use the MOD function i.e =MOD(A1,1)
will always return zero if A1 = 1, 2, 3 and so forth. If A1 = 1.19 or 0.19 then the MOD(A1,1) function will return the value 0.19

Alf

**MHRW** · 08-02-2011, 07:53 PM

Thanks Alf, Its still getting stuck at the same point though. I've attached a spreadsheet with an example of the data i'm trying to work with. The table on the right is how I want it to look (I've manually put in the formula / value for each line).

I appreciate the help!

**ConneXionLost** · 08-03-2011, 01:52 AM

Hi,

How about:

=IF(AND(ISNUMBER(C3)=FALSE,ISNUMBER(D3)=FALSE),"BBL",IF(ISNUMBER(C3)=FALSE,ABS((F3-D3)/((F3+D3)/2)),IF(ISNUMBER(D3)=FALSE,ABS((C3-F3)/((C3+F3)/2)),ABS((C3-D3)/((C3+D3)/2)))))

Cheers,

**Alf** · 08-03-2011, 05:53 AM

Its still getting stuck at the same point though.

That's not strange as the formula I gave you is supposed to work only with numbers.
In the workbook you posted you try to use the formula on a mixture of strings and numbers.

ConnecXionLost:
Not to happy about you jumping into this tread before I got a chance to answer but I must confess your solution (ISNUMBER) was better than mine (testing for "<") so I guess this is a case of "may the best man win".

MHRW beware that values in F column (Detection Limits) must be numbers as they will be used in calculation if C or D values are strings.

Alf

**arthurbr** · 08-03-2011, 06:01 AM

ConnecXionLost:
Not to happy about you jumping into this tread before I got a chance to answer

Hi Alf,

please keep in mind that every member can answer threads at any moment, no one never stated that the first one to answer has priority over others.

Suppose you went on vacation or your computer crashed badly. Is the OP obliged to wait for you to come back to get an answer???

**Alf** · 08-03-2011, 08:41 AM

Suppose you went on vacation or your computer crashed badly

Then I would not be back that quick to grumble. Yes the OPs must be kept happy by getting a fast and correct answer but what about the forum members who provide the answers? They must be kept happy as well or the forum will die.

Since this is the “Excel General” forum I’ll start a new tread in “The Water Cooler” forum and air a few of my questions and views linking to this thread.

Alf