Thanks:  0
Likes:  0

# Thread: Counting of smiliar type of data based on certain conditions

1. ## Counting of smiliar type of data based on certain conditions

Hi All

I have a set of data in a single column stated below :

 30.32 73.82 133.99 51.93 51.77 54.55 55.24 60.95 60.95 60.95 52.35
Now if i want to get the final result where all rows ending with .95 should be true or else false, how can i derive through simple excel formula.

Thanks in advance in anticipation to get positive response.

2. ## Re: Counting of smiliar type of data based on certain conditions

OMKARTHEPOWER,

Excel 2007
AB
130.32False
273.82False
3133.99False
451.93False
551.77False
654.55False
755.24False
860.95True
960.95True
1060.95True
1152.35False

Sheet1

Worksheet Formulas
CellFormula
B1=IF(RIGHT(A1,2)="95","True","False")

3. ## Re: Counting of smiliar type of data based on certain conditions

Another possibility......
Excel 2007
AB
130.32FALSE
273.82FALSE
3133.99FALSE
451.93FALSE
551.77FALSE
654.55FALSE
755.24FALSE
860.95TRUE
960.95TRUE
1060.95TRUE
1152.35FALSE

Sheet7

Worksheet Formulas
CellFormula
B1=TRUNC(A1-INT(A1),2) =0.95

4. ## Re: Counting of smiliar type of data based on certain conditions

Thanks a lot for the resolution.

one more query .. if I add another condition to it, it shows error.

If I say ending with .95 or .24,

then i suppose formula should be

=IF(RIGHT(A1,2)=or("95","24"),"True","False")

But it is showing error.

Thanks
Anand

5. ## Re: Counting of smiliar type of data based on certain conditions

Anand,
As hiker95 would appear to be offline, I'm sure he won't mind me answering.

Your use of OR needs to be.....

=IF(OR(RIGHT(A1,2)="95",RIGHT(A1,2)="24"),"True","False") for hiker's

or =OR(TRUNC(A1-INT(A1),2) =0.95,TRUNC(A1-INT(A1),2) =0.24) for mine.

Hope that helps.

6. ## Re: Counting of smiliar type of data based on certain conditions

Snakehips,

You beat me to it - thanks.

7. ## Re: Counting of smiliar type of data based on certain conditions

Thanks a lot Tony for helping me to resolve this issue.

8. ## Re: Counting of smiliar type of data based on certain conditions

Now if i wish to add certain conditions .. please refer to column below:

 126.64 118.64 413.92 13.39 58.39 58.39 13.39 58.39 206.09 226.09 206.09 23.28 171.51 206.09 171.51 171.51 206.09 206.09 171.54 224.38 224.38 224.38

Now if i want to get the final result stated with conditions as below :

1) Any figure after decimal which is divisible by 9 should be true( for e.g. .09,.18,.27,.36,.45,.54. and so on) or

2) Any figure before decimal which which ends with 13 or its multiple (to a maximum of two digit result.. i.e. to say till 91 as 104 becomes three digit result) should be true. Rest all shopuld be false.

9. ## Re: Counting of smiliar type of data based on certain conditions

Is there anyone who can help me out please?

Regards
Anand

10. ## Re: Counting of smiliar type of data based on certain conditions

Anand,

Try...Excel 2007
ABC
1126.64FALSEFALSE
2118.64FALSEFALSE
3413.92FALSEFALSE
413.39FALSETRUE
558.39FALSEFALSE
658.39FALSEFALSE
713.39FALSETRUE
858.39FALSEFALSE
9206.09TRUEFALSE
10226.09TRUEFALSE
11206.09TRUEFALSE
1223.28FALSEFALSE
13171.51FALSEFALSE
14206.09TRUEFALSE
15171.51FALSEFALSE
1652.51FALSETRUE
17206.09TRUEFALSE
18206.09TRUEFALSE
19171.54TRUEFALSE
2078.63TRUETRUE
21224.81TRUEFALSE
22224.38FALSEFALSE

Sheet9

Worksheet Formulas
CellFormula
B1=MOD(ROUND(A1-INT(A1),2)*100,9)=0
C1=MOD(INT(A1),13)=0

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•