Whilst I was searching through the channels on telly the other night I came across a 'Quiz Call' channel where you had to add ALL the number within a certain sum (see sample)
Sixteen minus five times seven multiply by nine add twelve
You not only have to do the sum you also have to add in all the less obvious numbers like the Six within Sixteen, plus also all the Roman numbers in each word like the I, X, & IX within the sixteen.
I've done a small example to show what I mean, but I don't have any idea how to create a formula that will add up all of all of the permitations of roman numerals within each word.
Can any body help?
Many Thanks and A Happy New Year 2007
Gary Hunt
Calculate Written Numbers



Re: Adding Every Number
No idea what you want & your sample doesn't match your description.

Re: Adding Every Number
Thanks for replying.
I'll try to explain a little better. What I need is a formula that will add all the number that make up a sum written in words like the example below
Sixteen Minus Five Times Seven Multiply By Nine Add Twelve
1. The sum above equals 705
2. There's also a Six within the sixteen adding another 6
3. The Minus contains a Roman M, MI, & I = 1000+1001+1=2002
4. The Five contains a Roman I, V, & IV = 1+5+4=10
5. The Times contains a Roman I, M, & IM = 1+1000+999=2000
6. The Seven contains a Roman V = 5
7. The Multiply contains a Roman M, L, I, L = 1000+50+1+50=1101
8. The Nine contains a Roman I = 1
9. The Add contains a Roman D, D = 500+500=1000
10. The Twelve contains a Roman L, V, & LV = 50+5+55=110
Is there a formula that will add up all of the roman numbers within any given word?
Thanks
Gary Hunt 
Re: Adding Every Number
Offtopic: Gary, you might be interested in this recent thread:
It's another game played in those programs. Searching a square made out of 4 dots is often played in Belkgium at night.
Now back on topic
Wigi

Re: Calculate Written Numbers
Well I think I did it...<again>...still not sure why!
There is (for a while) an eXample at:
Copy and paste this link into your browser address bar (no search) and then rightclick on "...  game show style", Save Target As...
It's limited to the words on the page but like I said it's an example!
E&OE.
Cheers,
dr


Re: Calculate Written Numbers
Thank You very much for that. Once again you guys have come up trumps. I now regularly recommend this forum to all my friends.
Thanks Again, and a Very Happy New Year to everybody in this forum.
Gary Hunt 
Re: Calculate Written Numbers
Thanks Gary. Wish you the same.
And well done dr. Nice work.

Re: Calculate Written Numbers
Hello all
[EDIT: attachment was replaced with an amended version. Better validation on the inputs of the Inputboxes.]
I wrote an extended version of the code of dr.
I first shortened his code to only 2 loops (in small arrays). Then, I extended the program to handle both English sentences and Dutch sentences. Via a simple Inputbox, you choose English or Nederlands. (= Dutch word for "Dutch" : D) Then you key in or paste the sentence. The results are outputted to the sheet.
See attachment. Comments are welcomed.
Greetz
Wigi

Re: Calculate Written Numbers
Wow!!! Is there anything you guys can't do under excel????
I look forward to trying this out next time the TV Quiz show is onThanks again
Gary Hunt

Re: Calculate Written Numbers
Quote from Garyhunt6809I look forward to trying this out next time the TV Quiz show is on
Gary
Just a remark... Be careful with these games, because the rules are not always so clear... The TV channels (at least in Belgium) earn abnormal profits. Sometimes they change the rules of the games without telling the spectators
From Jan. 1 onwards there's a law to forebid certain things, but seems like the channels keep on neglecting them at a regular basis.
About the Excel program:
 Double spaces, should you type them, are converted to single spaces.
 Comma's are neglected.
 Single spaces are the (assumed) separators between word numbers and operators.
 Numbers can go up to twenty, as with dr's code.
 Have a look at the 2 modules in the VBA code to see what is allowed, like:
times, multiplied, X, etc. for multiplication.Should you have comments, please write them down.
Cheers
Wigi


Re: Calculate Written Numbers
Hi There,
Thanks for the warning. I'll take care when considering when and where I enter these competitions. I don't know if the competitions are governed by the same rules in the UK as they are in Belguim????
Thanks Again
Gary Hunt

Re: Calculate Written Numbers
Hi Guys,
At least we're having _fun_!
Chhers,
dr
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!