Counting of smiliar type of data based on certain conditions

OMKARTHEPOWER

New Member
Joined
Nov 8, 2014
Messages
8
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

<tbody>
</tbody>
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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
OMKARTHEPOWER,

How about this?


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
Cell Formulas
RangeFormula
B1=IF(RIGHT(A1,2)="95","True","False")
 
Upvote 0
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
Cell Formulas
RangeFormula
B1=TRUNC(A1-INT(A1),2) =0.95
 
Upvote 0
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.

Can you please help guide me on the same.

Thanks
Anand
 
Upvote 0
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.
 
Upvote 0
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

<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>


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.

Thanks in advance.
 
Upvote 0
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
Cell Formulas
RangeFormula
B1=MOD(ROUND(A1-INT(A1),2)*100,9)=0
C1=MOD(INT(A1),13)=0
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top