If & Then

[Yo]

This is a tought one..........

I'm trying to make an IF function but don't know if what I want is possible.

In F33 I have 185,000.00 (this number changes every month)

If the user puts the correct verification code in F35 then L35 will say
"OK", If not, L35 will say "Wrong Code"

The problem is, to get the verification code on 185,000,00 one would count
the amount of digits in the number and then add the value of the digits.

185,000.00 is eight digits long, so the calculation would be:

8+1+8+5+0+0+0+0+0 = 22

So the verification code would be 22.


Is there such a formula in excel that could figure out the verification
code?



Thanks to anyone who can help me or who can tell me I'm crazy.

Yosemite

 

[Guest]

For a value in A1, try this:

=LEN(A1*100)+SUMPRODUCT(--MID(A1*100,ROW(INDIRECT("1:"&LEN(A1*100))),1))

Is that something you can work with?

••••••••••
Regards,
Ron

 

[Yo]

That works perfectly! Thank you so much, I really appreciate the help.

Sincerely,
Yosemite

 

[Rowan Drummond]

Maybe with a UDF:

Function Verify(Total As Range, VerCode As Range)
Dim strTot As String
Dim i As Integer
Dim rslt As Integer
strTot = Replace(Total.Text, ",", "")
strTot = Replace(strTot, ".", "")
rslt = Len(strTot)
For i = 1 To Len(strTot)
rslt = rslt + CInt(Mid(strTot, i, 1))
Next i
If VerCode.Value = rslt Then
Verify = "OK"
Else
Verify = "Wrong Code"
End If
End Function

Paste this into a standard module and then in L35 you can enter
=Verify(F33,F35)

Hope this helps
Rowan

 

[CLR]

Beautiful formula Ron, and works great with the OP's sample data, however,
it does require a two place decimal number in A1 to work. Both 185000.00
and 185000 give a result of 22.

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

True, Chuck....but aren't the numbers 185000 and 185000.00 stored the same
way in Excel, regardless of how they're displayed? There's no way to enter
the number 185000.00 and have excel keep the zero pennies. That number in the
formula bar is displayed as 185000.

So, I went with the following assumptions:
1)The values to be tested are numeric, not text.
2)The OP was trying to use a check-digit on dollar amounts. This assumption
was based on the statement that 185000.00 contained 8 digits.

••••••••••
Regards,
Ron

 

[CLR]

You're right, of course, and maybe the figure actually is "dollars and
cents", but in any event, I guess there will be no problem as long as the OP
has A1 formatted to show the two decimal places, so his user can see what
two digets to figure into the code..........they might not always be zeros.

Again, my compliments on a really nice formula, Ron

Vaya con Dios,
Chuck, CABGx3



Vaya

 

[Guest]

Thanks, Chuck
.....and excellent point:

as long as the OP has A1 formatted to show the two decimal places<

You're right. If the users couldn't SEE the decimal places, they wouldn't
know to include them in the validation calculation.

••••••••••
Regards,
Ron

 

[Yo]

Rowan,
This formula works as well, thank you! And though the user will always round
to the nearest dollar on my form, this would fix the concerns for the
replies above. In any case, both formulas fulfill my needs and I will keep
both on file for future reference. Now my only problem is deciding which
one to use....

Again, Thank you both for your help.

Yosemite

 

[Rowan Drummond]

You're welcome.

While I share Chuck's admiration for Ron's formula (I would never have
guessed it could be done so neatly) I was concerned that the value in
F33 may have more than 3 decimal places (eg if it was a calculation) and
the formatting was showing that value rounded to two decimal places. It
is for this reason that I used the .Text property of the cell which is
what you see on the screen as apposed to the .Value property which is
the actual value and what would appear in the formula bar if it was just
typed in.

Regards
Rowan

 

[Guest]

I'm not sure if this matters in your application of the formula, but if the
user enters more that 2 decimal places (example: 123.456) the formula
currently returns an error.

That error could be trapped with this version of the formula
=IF((A1*100)=INT(A1*100),LEN(A1*100)+SUMPRODUCT(--MID(A1*100,ROW(INDIRECT("1:"&LEN(A1*100))),1)),"invalid number")

Consequently, entering 123.111 would return "invalid number" instead of
#VALUE!.

Does that help?

••••••••••
Regards,
Ron