Question
Hoverstate
US
Last activity: 1 Jun 2022 10:03 EDT
Excel formula doesn't calculate correctly
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
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
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.
Accepted Solution
Updated: 20 Jul 2021 11:37 EDT
Hoverstate
US
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!
Pegasystems Inc.
US
@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?
Hoverstate
US
@ThomasSasnettWhen 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?
Accepted Solution
Updated: 20 Jul 2021 11:37 EDT
Hoverstate
US
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!
Pegasystems Inc.
US
@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
Updated: 31 May 2022 14:13 EDT
PNC Bank
US
@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?
Pegasystems Inc.
US
@VittalS6Please 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.