Question

4
Replies
33
Views
Daniel Quinlog (DanQuinlog)
Hoverstate

Hoverstate
US
DanQuinlog Member since 2020 4 posts
Hoverstate
Posted: 1 week 4 days ago
Last activity: 5 days 21 hours ago
Posted: 15 Jul 2021 17:13 EDT
Last activity: 21 Jul 2021 9:00 EDT
Solved

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

set workbook and sheet => open => set cell formula => calculate => save => retrieve value in message, displays 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

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.

***Edited by Moderator: Pooja Gadige to add platform capability tag***
Pega Robotic Process Automation 19.1 Robotic Process Automation Robotics System Architect