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

[Ron Coderre]

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" wrote:

> 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
>
>
>

[Yo]

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

Sincerely,
Yosemite


"Ron Coderre" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
>> 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
>>
>>
>>

[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

Yo wrote:
> 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
>
>

[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



"Ron Coderre" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > 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
> >
> >
> >

[Ron Coderre]

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" wrote:

> 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
>
>
>
> "Ron Coderre" <[email protected]> wrote in message
> news:[email protected]...
> > 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" wrote:
> >
> > > 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
> > >
> > >
> > >
>
>
>

[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
"Ron Coderre" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > 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
> >
> >
> >
> > "Ron Coderre" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > >
> >
> >
> >

[Ron Coderre]

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


"CLR" wrote:

> 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
> "Ron Coderre" <[email protected]> wrote in message
> news:[email protected]...
> > 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" wrote:
> >
> > > 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
> > >
> > >
> > >
> > > "Ron Coderre" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" wrote:
> > > >
> > > > > 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
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

[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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> Yo wrote:
>> 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

[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

Yo wrote:
> 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" <[email protected]> wrote in message
> news:[email protected]...
>
>>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
>>
>>Yo wrote:
>>
>>>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
>
>
>

[Ron Coderre]

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


"Yo" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> > Yo wrote:
> >> 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
>
>
>