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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Show the working example too. Need to see what you are aiming for.
 
Upvote 0
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
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
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
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
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,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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