cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
emoii
Level: Powered On

Make excel like calculations in PowerApps

Hi all

 

I struggle with calculations in PowerApps. In excel I am able to create the form, and get the desired output, but I want to create an app letting user make those calculations without having to access excel. What I have is: 

 

SharePoint list with my values to calculate with, the title is the name of the products, and then I have 4 columns with valuse, RangeMin, RangeMax, ValidationMin, ValidationMax.

 

Besides those values that do not change that often (but could be updated by an admin), I have empty fields where the user inputs some numbers. In the end I have a Score field that displays the calculation, a mix of the user input numbers and the values from SharePoint.

 

In Excel I can do a formula and pick and choose calues to calculate, but how can I do it in PowerApps, that is, how can I say (Title3.ValidationMax - Title3.Validation.Min) / (Title3.RangeMax-Title3.RangeMin).

 

I am unaware how to exactly point/refer the different values. I could maybe create a lot of variables, and let the Admin update those in the PowerApp and get rid of the SharePoint list, but is this a better approach then using the SharePoint list?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Make excel like calculations in PowerApps

Hi @emoii ,

Yes, you can target specifik "cells" .

I suggest you try FirstN and LastN function.

Using these two functions can target specifik "cells".

Try this formula:

(Value(FirstN(collectionname,3).ValidationMax)-Value(FirstN(collectionname,3).ValidationMin)) / 
(Value(FirstN(collectionname,2).RangeMax)-Value(FirstN(collectionname,2).RangeMin))

Here's doc about these two functions:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-first-last

 

Best regards,

Community Support Team _ Phoebe Liu

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Highlighted
Community Support Team
Community Support Team

Re: Make excel like calculations in PowerApps

Hi @emoii ,

Could you tell me what does Title3 represent?

I suggest you try this formula:

ClearCollect(collection1,AddColumns(tablename,"result",(ValidationMax-ValidationMin) / (RangeMax-RangeMin)))

Then, the result will display in the new field "result".

But AddColumns function is used to display data not update data.

Here's a doc about AddColumns for your reference:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-table-shaping

If you want to update, I suggest you use ForAll function.

Try this formula:

ForAll(collection1,Patch(tablename,LookUp(tablename,Id=collection1[@Id]),
{result:(ValidationMax-ValidationMin) / (RangeMax-RangeMin)}))

Here's a doc about updating multiple records for your reference:

https://powerapps.microsoft.com/en-us/blog/bulk-update-using-forall-and-patch/

 

 

Best regards,

Community Support Team _ Phoebe Liu

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
emoii
Level: Powered On

Re: Make excel like calculations in PowerApps

If I have a collection with 5 columns, Title, RangeMin, RangeMax, ValidationMin, ValidationMax, and in that collection there is 5 rows, lets say that, see attached image.

 

Then, is it possible to use that Collection to make formulas and calculations. What I want is to make a formula that says, (ValidationMaxTitle3-ValidationMinTitle3) / (RangeMaxTitle2-RangeMinTitle2).

 

So can I target specifik "cells" in the collection like I can in a Excel Sheet? What I did now was to create Text Input Controls on a seperat screen, and save each Text Input Control as a variable. But would love to use Collection or SharePoint list if possible, because when an Admin wants to update the values on the Text Input Controls, it is only done for that session, when the app is closed the "new" values are not saved for all users in global variables.

Community Support Team
Community Support Team

Re: Make excel like calculations in PowerApps

Hi @emoii ,

Yes, you can target specifik "cells" .

I suggest you try FirstN and LastN function.

Using these two functions can target specifik "cells".

Try this formula:

(Value(FirstN(collectionname,3).ValidationMax)-Value(FirstN(collectionname,3).ValidationMin)) / 
(Value(FirstN(collectionname,2).RangeMax)-Value(FirstN(collectionname,2).RangeMin))

Here's doc about these two functions:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-first-last

 

Best regards,

Community Support Team _ Phoebe Liu

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 139 members 4,786 guests
Please welcome our newest community members: