# Formula to convert textual number values array to actual number values

#### Preacherman771

##### New Member
I am trying to count the number of each group of duplicate values in column O25:O40 using COUNTIF(\$O\$25:\$O\$40,"0.688"). Knowing that the values in that column are textual and not actual numeric values, how would I go about changing them to numeric as part of my formula. Or, do I need to first create a helper column? Any help is greatly appreciated.

NFL 2021-2022 Standings (Template).xlsx
HIJKLMNO
23
24Rnd 1TeamTeamPct
251KC---
262BUF---
273PIT---
28--TBDBAL0.688
29--TBDCLE0.688
30--TBDIND0.688
31--TBDTEN0.688
328MIA---
339LV---
34--TBDNE0.438
35--TBDLAC0.438
3612DEN---
3713CIN---
3814HOU---
3915NYJ---
4016JAX---
Calc_Ties (Conf)
Cell Formulas
RangeFormula
J25:J40J25=IFERROR(INDEX(\$H\$7:\$H\$22,MATCH(K25,\$I\$7:\$I\$22,0)),"--")
K25:K40K25=IF(AND(K7=K8,OR(ROWS(K\$25:K25)*COLUMNS(K\$25:K25)=1,ROWS(K\$25:K25)*COLUMNS(K\$25:K25)=16)),"TBD", IF(OR(K6=K7,K7=K8),"TBD",I7))
M25:M40M25=IF(K25<>"TBD","---",I7)
O25:O40O25=IF(M25<>"---",INDEX(\$K\$7:\$K\$22,MATCH(M25,\$I\$7:\$I\$22,0)),"")

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=IF(M25<>"---",INDEX(\$K\$7:\$K\$22,MATCH(M25,\$I\$7:\$I\$22,0)+0),"")``

#### Preacherman771

##### New Member
Thanks, but when I entered COUNTIF(\$O\$25:\$O\$40,"0.688") after changing the formula in column O25:O40 to your suggested formula, I receive a result of 0 and not the number of times 0.688 appears. Any other suggestions?

#### Fluff

##### MrExcel MVP, Moderator
That would suggest that you numbers don't exactly = 0.688
If your current formulae work, why not leave it as-is.

#### Preacherman771

##### New Member

The original COUNTIF did not work either with my current formula, thus the request for the ability to convert over within the same formula. Or is the only recourse I have is to create a helper column which incorporates the cells using NUMBERVALUE?

#### Fluff

##### MrExcel MVP, Moderator
You could round the values like
Excel Formula:
``=IF(M25<>"---",ROUND(INDEX(\$K\$7:\$K\$22,MATCH(M25,\$I\$7:\$I\$22,0))+0,3),"")``

#### Preacherman771

##### New Member
Thank you for you responses and suggestions.

#### Fluff

##### MrExcel MVP, Moderator
Glad to help & thanks for the feedback.

Replies
2
Views
169
Replies
1
Views
55
Replies
2
Views
446
Replies
1
Views
93
Replies
5
Views
113

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,697
Messages
5,765,987
Members
425,320
Latest member
Galin

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

### Which adblocker are you using?

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

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