CODEWITHSUNDEEP
c#vbaexcelexcel-formula
JPaulPunzalan
JPaulPunzalan

Reputation: 427

Converting a long nested Excel formula to c#

I am tasked to transform an Excel VBA to a web-based application. I am using Web Forms. I am stuck with a long Excel formula but I have converted half of the long formula into C#.

Here is the Excel formula:

IF(AND(D7="u", H7/F7>1), 0, INDEX(Scoring!$O$8:$O$10, SUMPRODUCT(--(E28 <= Scoring!$N8:$N10),--(E28 >= Scoring!$N8:$N10), ROW(INDIRECT("'Scoring'!$M1:$M3"))))

While here is my progress on the C# method. I need help on the ELSE part:

IF (variable = "u" && ((CurrentValue / AcceptedValue) > 1)){
    return 0;
ELSE {
    // the INDEX clause on the Excel Formula
}

O8 - O10 values are {2, 1, 0}

N8 - N10 values are {1, 1.5, 9999999999}

E28 is 0

I have also got the result of the remainder of the formula piece by piece. I just need guidance on how to put it in C#.

=INDEX(Scoring!$O$8:$O$10, SUMPRODUCT({1,1,1}, {0,0,0}, 1))

The double minus(--) made it return 0/1 instead of true/false. ROW(INDIRECT($M1-$M3)) is equivalent to 1.

I could use some help on transforming the Excel formula into C# code.

Upvotes: 2

Views: 256

Answers (2)

Slai
Slai

Reputation: 22876

ROW(INDIRECT("'Scoring'!$M1:$M3")) actually evaluates to {1; 2; 3} and

--(E28 <= Scoring!$N8:$N10),--(E28 >= Scoring!$N8:$N10) is --(E28 = Scoring!$N8:$N10)

so the SUMPRODUCT formula is just:

(E28 = Scoring!$N8) * 1 + (E28 = Scoring!$N9) * 2 + (E28 = Scoring!$N10) * 3

and in C#:

double[] n8n10 = { 1, 2, 3 }; double e28 = 2; 

double sumProduct = n8n10.Select((d, i) d == e28 ? i + 1 : 0).Sum();

But! because Scoring!$O$8:$O$10 is only 3 cells and the SUMPRODUCT can in theory result in more than 3, the actual aim of the INDEX SUMPRODUCT part seems to be something like this:

double[] o8o10 = { 4, 5, 6 }, n8n10 = { 1, 2, 3 }; 
double e28 = 2, result = o8o10[0];                     // INDEX(array, 0) results in array

if      (n8n10[1] == e28) result = o8o10[1];
else if (n8n10[2] == e28) result = o8o10[2];

Upvotes: 0

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

Here is some C# code to replicate this part of your formula:

INDEX(Scoring!$O$8:$O$10, SUMPRODUCT(--(E28 <= Scoring!$N8:$N10),--(E28 >= Scoring!$N8:$N10), ROW(INDIRECT("'Scoring'!$M1:$M3")))

In the code I've had to assign the array {1, 1, 1} for ROW(INDIRECT("'Scoring'!$M1:$M3")). There is some ambiguity in your question around the action of SUMPRODUCT. According to support.office.com

Multiplies corresponding components in the given arrays, and returns the sum of those products.

But some of your detail in the question makes me think you are thinking that each array get summed up and then those results get multiplied together. Anyway, check out the comments in the code below to follow the logic:

// inputs
double[] o8o10 = new double[] { 2, 1, 0 }; // Scoring!$O$8:$O$10
double[] n8n10 = new double[] { 1, 1.5, 9999999999 }; // Scoring!$N8:$N10
double e28 = 0; // E28

// entries to SUMPRODUCT
List<int> test1 = new List<int>();
List<int> test2 = new List<int>();
Array.ForEach(n8n10, x => { test1.Add((e28 <= x) ? 0 : 1); });
Array.ForEach(n8n10, x => { test2.Add((e28 >= x) ? 0 : 1); });
// ROW(INDIRECT("'Scoring'!$M1:$M3")) should be an array !
List<int> test3 = new List<int> { 1, 1, 1 }; 

// evalue SUMPRODUCT
int sumProductResult = 0;
for (var i=0; i<test1.Count; i++)
{
    sumProductResult += test1[i] * test2[i] * test3[i];
}

// evalute INDEX
double indexResult = 0;
indexResult = o8o10[sumProductResult];

// output
Console.WriteLine(indexResult);
Console.ReadKey();

The output for me is 2 because that is the 0th element of the o8o10 array. We get 0 for the array index because

=SUMPRODUCT({1, 1, 1}, {0, 0, 0}, {1, 1, 1})

resolves to:

=SUM(1*0*1, 1*0*1, 1*0*1}

Which gives 0.

HTH

Upvotes: 1

Related Questions