CODEWITHSUNDEEP
c#excelexcel-formula
Oscar Mederos
Oscar Mederos

Reputation: 29823

Converting some Excel formula to C#

I'm trying to convert some Excel formulas into C# code, and I'm kind of lost...

I have the following:

SUMPRODUCT(1*(RIGHT(A1)={"a","e","i","n","y"}))

What exactly does that mean?

Here is what I know:

but here is what I don't understand:

If the text is Claude, for example...

RIGHT(A1)={"e","a","b","c","d"} returns TRUE

and

RIGHT(A1)={"a","b","e","c","d"} returns FALSE

I only changed the index position of the e character.

What is happening there?
What I'm not understanding?

Upvotes: 3

Views: 2263

Answers (2)

Ta01
Ta01

Reputation: 31610

If you want one line based on @CoryLarson's suggestion that will work for any set of letters:

Func<string, string[], int> LetterCount = (x, y) => y.Contains(x.Substring(x.Length-1, 1)) ? 1 : 0;

Upvotes: 1

mellamokb
mellamokb

Reputation: 56769

Basically the formula is checking if the last character in cell A1 is any of the following characters: a, e, i, n, or y. The SUMPRODUCT part is important because it is a hack to check the entire array at once against the last character. When you strip that part out and just use RIGHT(A1)={"a","b","e","c","d"}, Excel actually only looks at the first entry in the array, checks to see if it's a match, and returns immediately. So when 'e' is in the first position, you get True.

SUMPRODUCT allows the checking to be applied across the entire array. Another way to see this would be to manually type it out into separate cells in a grid fashion like this

       A   |       B      |  C  |      D
1 | Claude | =RIGHT(A1,1) | 'a' | =1*(B1=C1)
2 |        | =RIGHT(A1,1) | 'e' | =1*(B2=C2)
3 |        | =RIGHT(A1,1) | 'i' | =1*(B3=C3)
4 |        | =RIGHT(A1,1) | 'n' | =1*(B4=C4)
5 |        | =RIGHT(A1,1) | 'y' | =1*(B5=C5)
6 |        |              |     | =SUM(D1:D5)

The bottom right cell would contain a 1 if any of the characters a,e,i,n,y are at the end of the value in A1, or a 0 if not. I am manually performing the same logic as SUMPRODUCT to get to the same result.

So, how this would be accomplished in C#.Net:

var checkValue = "Claude";
var letters = {"a", "e", "i", "n", "y"};
var found = 0;

foreach (var theLetter in letters)
    if (checkValue.EndsWith(theLetter))
        found = 1;

return found; // returns same value as Excel function

Upvotes: 8

Related Questions