Getting a Error Message In Formula

CARBOB

Well-known Member
Joined
Jun 6, 2005
Messages
1,870
The formula in Cell GY2 works when the value in Cell Gu2 is in 3 different cells, but I'm trying to save space, It should be returning the value in Cell GV2. I get a error message. Where am I going wrong?
UNHIT COMBOS AFTER 1000 DRAWS.xls
GUGVGWGXGYGZHA
2000EEELLLOOO0=MCONCAT(IF(MOD($GU2,{1,2,3},2)=0,"E","O"))&MCONCAT(IF(GU2,{1,2,3},<5,"L","H"))&MCONCAT(IF(MOD(GU2,{1,2,3},8)<3,"O","I"))&(MOD(SUM(GU2,{1,2,3},10))*1
3001
4002
5003
6004
7005
8006
9007
10008
11009
COMBOS
 

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.

CARBOB

Well-known Member
Joined
Jun 6, 2005
Messages
1,870
Show the working example too. Need to see what you are aiming for.

This is the working example. I'm hoping to get the formulas to read the 3-digit numbers in column HA instead of each cell, to free up 2 columns. Thanks.
UNHIT COMBOS AFTER 1000 DRAWS.xls
GWGXGYGZHAHBHC
708/13/07919919OOOHLHOOO9
808/12/07233233EOOLLLOII2
908/11/0701010EOELLLOOO0
1008/10/07506506OEEHLHIOI5
1108/09/07989989OEOHHHOOO9
1208/08/07263263EEOLHLOII2
1308/07/07721721OEOHLLIOO7
1408/06/07443443EEOLLLIII4
15
16
17
18
COMBOS
 
Upvote 0

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
I'm not sure about the last bit because I can't see your working formula, but for a start try:

Code:
=MCONCAT(IF(MOD((MID($GU2,{1,2,3},1)+0),2)=0,"E","O"))&MCONCAT(IF((MID(GU2,{1,2,3},1)+0)<5,"L","H"))&MCONCAT(IF(MOD((MID(GU2,{1,2,3},1)+0),8)<3,"O","I"))&MOD(SUM((MID(GU2,{1,2,3},1)+0)),10)

Basically you need to use MID to extract the individual numbers.
 
Upvote 0

CARBOB

Well-known Member
Joined
Jun 6, 2005
Messages
1,870
I'm not sure about the last bit because I can't see your working formula, but for a start try:

Code:
=MCONCAT(IF(MOD((MID($GU2,{1,2,3},1)+0),2)=0,"E","O"))&MCONCAT(IF((MID(GU2,{1,2,3},1)+0)<5,"L","H"))&MCONCAT(IF(MOD((MID(GU2,{1,2,3},1)+0),8)<3,"O","I"))&MOD(SUM((MID(GU2,{1,2,3},1)+0)),10)

Basically you need to use MID to extract the individual numbers.

Thanks Andrew, now I'm getting a #Value message.
 
Upvote 0

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
If G2 contains the text entry 989 my formula returns OEOHHHOOO6. You will get #VALUE if the entry in G2 is less than 3 characters.
 
Upvote 0

CARBOB

Well-known Member
Joined
Jun 6, 2005
Messages
1,870
If G2 contains the text entry 989 my formula returns OEOHHHOOO6. You will get #VALUE if the entry in G2 is less than 3 characters.


I see that now. How do I get it to read the values 000 thru 099? I formatted the ceels as number, then I Custom formatted with 3 zeroes. That didn't work either.
 
Upvote 0

Forum statistics

Threads
1,191,420
Messages
5,986,464
Members
440,031
Latest member
davidvillegasr

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