cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
emoii
Resolver I
Resolver I

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

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
v-yutliu-msft
Community Support
Community Support

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.

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.

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (2,408)