Excel formula doesn't calculate correctly

General
, ,
1

I am running an automation that takes a csv worksheet and applies a pretty simple formula

This is the worksheet

a a 1 a a
a b 1 a b
b a 1 b a
b b 1 b b
a a 1
a b 1
b a 1
b b 1
a a 1
a b 1

The formula is

=SUMPRODUCT(($C$1:$C$10*($A$1:$A$10=D1)*($B$1:$B$10=E1)))

Here is the automation

set workbook and sheet => open => set cell formula => calculate => save => retrieve value in message, displays 1.jpg
set workbook and sheet => open => set cell formula => calculate => save => retrieve value in message, displays 1.jpg1614×589 179 KB

When I enter the formula in openoffice calc or excel it calculates properly. When I enter the formula in my automation and calculate I receive only 1 as the value.

I am using Excel Connector, same result with both .csv and .xlsx file types.

Thank you for any information.

2

@DanQuinlog I just tried a simpler formula on a sheet I created and it works with either CSV or XLSX, so I don’t think that is the issue. I think your issue might be with the formula itself. Can you try with just a simple formula like “=SUM(D2:D4)” and see what happens?

3

@ThomasSasnett When I do a more simple formula like =SUM(C1:C10) it works, but of course that isn’t the formula I’m trying to apply. When I tried SUMIFS and SUMIF I got only errors. After testing and double checking my formula was put together right, I guess the answer is those just aren’t supported. SUMPRODUCT is older so I figured it might be supported, and appears to be supported since I am getting a value not an error, but its not returning the right value.

Is there any documentation about what formula are or are not supported with Excel Connector?

4

I’ve found that if I open the spreadsheet using Microsoft Connector to let the formula calculate, save and re-open with excel connector that it worked. I’ll play more with that I guess. Thanks!

5

@DanQuinlog Here are the currently supported functions:

ABS, ACOS, ACOSH, ACOT, ACOTH, ADDRESS, AND, ASIN, ASINH, ATAN, ATAN2, ATANH, AVEDEV, AVERAGE, AVERAGEA, AVERAGEIF, AVERAGEIFS, CEILING, CEILING.PRECISE, CHAR, CHOOSE, CLEAN, CODE, COLUMN, COMBIN, CONCATENATE, COS, COSH, COT, COTH, COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, DATE, DATEDIF, DATEVALUE, DAY, DAYS, DAYS360, DEGREES, DOLLAR, EDATE, EOMONTH, EVEN, EXACT, EXP, FACT, FALSE, FIND, FIXED, FLOOR, FV, HLOOKUP, HOUR, IF, IFERROR, IFNA, IFS, INDEX, INDIRECT, INT, IPMT, IRR, ISBLANK, ISERR, ISERROR, ISEVEN, ISFORMULA, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISODD, ISREF, ISTEXT, LARGE, LEFT, LEN, LN, LOG, LOG10, LOOKUP, LOWER, MATCH, MAX, MAXA, MAXIFS, MID, MIN, MINA, MINIFS, MINUTE, MEDIAN, MOD, MODE, MONTH, N, NA, NETWORKDAYS, NORMSDIST, NOT, NOW, NPV, ODD, OFFSET, OR, PI, PMT, POWER, PPMT, PRODUCT, PROPER, QUOTIENT, RADIANS, RAND, RANDBETWEEN, RANK, RANK.AVG, RANK.EQ, RATE, REPLACE, REPT, RIGHT, ROUND, ROUNDDOWN, ROUNDUP, ROW, SEARCH, SECOND, SIGN, SIN, SINH, SQRT, STDEV, STDEVA, STDEVP, STDEVPA, SUBSTITUTE, SUBTOTAL, SUM, SUMIF, SUMIFS, SUMPRODUCT, SUMSQ, T, TAN, TANH, TEXT, TIME, TIMEVALUE, TODAY, TRIM, TRUE, TRUNC, TYPE, UPPER, VALUE, VAR, VARA, VARP, VARPA, VLOOKUP, WEEKDAY, WEEKNUM, XIRR, XLOOKUP, XMATCH, YEAR, YEARFRAC

6

@jeffbadger - I do not see YEARFRAC function in Pega 8.6.1. DateTimeDifference will not help as it returns output in integer format. Our use case is to return the decimal value same as YEARFRAC in excel. Can you please help?

7

@VittalS6 Please create a new post for this. This post was about using Pega Robotics and applying a formula to a cell in Microsoft Excel. I am not sure your question is related to Pega Robotics, so a new post with the proper tags might get more visibility.