S
Steve
Can anyone tell me how to adjust this formula so that it correctly
calculates the value.
So I have cells with 5 numbers each seperated by a hyphen (-) for
example
21-1-2-4-5
What I would like to do is calulate the total based on the formula
below
=IFERROR(CHOOSE(LEFT(FL5232,1),5,3,2,1,1),0)+IFERROR(CHOOSE(MID(FL5232,LEN(FL5232)-2,1),5,3,2,1,1),0)
However when I try in the example above instead of returning the value
of 5 it returns 8 as it looks at the first number of the first entry
21 and uses the 2 to return a value of three then uses the next number
1 to return a value of five and therefore a total of 8.
However, is there anyway to amend the formula so that it looks at the
complete number 21 and therefore returns a 0 as it is not a number
between 1 and 5 as per the formula.
Hope that makes sense and thanks in advance
Steve
calculates the value.
So I have cells with 5 numbers each seperated by a hyphen (-) for
example
21-1-2-4-5
What I would like to do is calulate the total based on the formula
below
=IFERROR(CHOOSE(LEFT(FL5232,1),5,3,2,1,1),0)+IFERROR(CHOOSE(MID(FL5232,LEN(FL5232)-2,1),5,3,2,1,1),0)
However when I try in the example above instead of returning the value
of 5 it returns 8 as it looks at the first number of the first entry
21 and uses the 2 to return a value of three then uses the next number
1 to return a value of five and therefore a total of 8.
However, is there anyway to amend the formula so that it looks at the
complete number 21 and therefore returns a 0 as it is not a number
between 1 and 5 as per the formula.
Hope that makes sense and thanks in advance
Steve