Formula to convert textual number values array to actual number values

Preacherman771

New Member
Joined
Jun 15, 2021
Messages
27
Office Version
  1. 365
Platform
  1. Windows
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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=IF(M25<>"---",INDEX($K$7:$K$22,MATCH(M25,$I$7:$I$22,0)+0),"")
 

Preacherman771

New Member
Joined
Jun 15, 2021
Messages
27
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 15, 2021
Messages
27
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
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),"")
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 
Learn Excel from Bill Jelen

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

Forum statistics

Threads
1,151,700
Messages
5,766,007
Members
425,322
Latest member
galaxy6623top

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
Top