cancel
Showing results for
Did you mean:
Helper III

Mirroring simple excel sheet in Powerapps

Hi,

This is my first step into power apps and I'm not sure where to start so any help would be useful.

I have an excel sheet that calculates the time a fridge will get down to a certain temperature.

There are 2 user inputs: Current temperature and Current mean temperature found on the fridge thermometer.

I then have a simple table shown below:

The first and second columns are the user input.

The third column (Mean 2) has the following expression:

=Round(Average('Whole of column 1','All of column 2 up to that row'),2)

E.g. the first row is the average of the whole of column 1 and the first row of column 2. The second row is the average of the whole of column 1 and the first 2 rows of column 2. Etc

My aim is to get to an average of 5. So my final result is a vlookup to find the time where column 3 = 5.

Using this excel is inconvenient on a phone in the field so i wanted to make a mobile app to make this more user friendly. I cant just import the table into PowerApps as it contains formulas. I have looked on line for ways to create the formulas inside of power apps but its hard to find relevant examples. I assume i need to import static data which will need to be the times and maybe an ID column to help with the lookup. And maybe the first 3 columns will be calculated inside power apps?

What is the best way to create what i am looking for? is it possible? This may be a lot to ask so if you have a solution that's amazing, but if not, any links to read that may be more relevant to me would also be very helpful.

Thanks

Sam

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

@mr-samd  oops, yip you correct please replace that hardcoded value

you are most welcome.

please check that your mean and temp values are not in reverse, that will yield a 0. i noticed you have temp on top where I have mean - should give the same answer as long as the values captured are in the right box 🙂

``````With(
{
_data: With(
{
_n: 288,
_v: 288 * txtMean.Text //replace the 5.2 with this
},
ForAll(
Sequence(
_n,
1
),
{
_t: Value * 5,
_tp: Round(
(_v + txtTemp.Text * Value) / (_n + Value),
2
)
}
)
)
},
With({_iterations: LookUp(_data, _tp = 5 )._t
},
RoundDown(_iterations/60,0) & ":" & Mod(_iterations, 60) & ":00"
)
)``````

Textboxes are formatted as numbers right

hey there if you liked the post give it a thumbs up, and if it solved your question please accept it as a solution.
11 REPLIES 11
Super User

hi   @mr-samd

this is very possible in Power Apps.

when you start the app you need the mean of col1. the data you display will have a static number right through so i assume that mean1 has some more values than just 5.2, a number close to 7 is expected.

Further, a mean of 5.2 and an entry of 3.4 constantly, will that not keep your rounded number the same?

Anyways let see if this is what you looking for ;). (

Add a button, Galley and a text box

OnVisible i added the code, but you can get it froma calc or set your own

Set(varMean, 5.2)

On the button OnSelect add the code

``````ClearCollect(colTemp,{_temp: TextInput1.Text});

Collect(
)``````

add the gallery items = colTemp2

varMean | ThisItem._temp | Text(ThisItem.mean2, "##0.00")

just a method you can refine the calcs as you wish.)

In Excel this is what i get for the numbers below

Result

Hope it helps,

R

hey there if you liked the post give it a thumbs up, and if it solved your question please accept it as a solution.
Helper III

Hi, Thanks @rubin_boer! this is a great help.

But i may not have been clear.

Mean 1 and the current temp are entered by the user. They are found from the fridge thermometer.

They are entered in every cell so individually the average would be 5.2 and 3.4 respectively. But Mean 2 includes all of Mean 1 but only the rows above the cell you are calculating for Current temp. So the amount of cells averaged increases by 1 for each new line.

I have attached my file to help try and make this more clear

Thanks again

Sam

Super User

@mr-samd  ok got it.

Add two textboxes (no surprise there :)).

• txtMean
• txtTemp

``````With(
{
_data: With(
{
_n: 288,
_v: 288 * 5.2
},
ForAll(
Sequence(
_n,
1
),
{
_t: Value * 5,
_tp: Round(
(_v + txtTemp.Text * Value) / (_n + Value),
2
)
}
)
)
},
With({_iterations: LookUp(_data, _tp = 5 )._t
},
RoundDown(_iterations/60,0) & ":" & Mod(_iterations, 60) & ":00"
)
)``````

Result

Hope i t helps,

R

hey there if you liked the post give it a thumbs up, and if it solved your question please accept it as a solution.
Helper III

That looks exactly what Im looking for thanks! Thanks @rubin_boer

Is this in the X tab of the label? Sorry, i am a complete newbie to this. How do i add the formula to the label?

Super User

after adding the label, select its  Text property, that's where you added 😉 as below:

hey there if you liked the post give it a thumbs up, and if it solved your question please accept it as a solution.
Helper III

Ok thanks @rubin_boer. Im sorry for all of the novice questions but this is really helping!

I have labeled the 2 text boxes as explained and I'm not getting the same result as you.

Also i can see in the code that you have 5.2 hard coded in. Should this be referencing the current mean (txtMean) instead?

Thanks

Sam

Super User

@mr-samd  oops, yip you correct please replace that hardcoded value

you are most welcome.

please check that your mean and temp values are not in reverse, that will yield a 0. i noticed you have temp on top where I have mean - should give the same answer as long as the values captured are in the right box 🙂

``````With(
{
_data: With(
{
_n: 288,
_v: 288 * txtMean.Text //replace the 5.2 with this
},
ForAll(
Sequence(
_n,
1
),
{
_t: Value * 5,
_tp: Round(
(_v + txtTemp.Text * Value) / (_n + Value),
2
)
}
)
)
},
With({_iterations: LookUp(_data, _tp = 5 )._t
},
RoundDown(_iterations/60,0) & ":" & Mod(_iterations, 60) & ":00"
)
)``````

Textboxes are formatted as numbers right

hey there if you liked the post give it a thumbs up, and if it solved your question please accept it as a solution.
Helper III

@rubin_boer Yes the textboxes are formatted as numbers...

Is there anything else in the code that I need to edit? I think i understand whats being done. What is the 'value' referring to?

Have i said thanks for all your help yet?!

S

Super User

Nope everything is ok as you have it.

The Value in an incremental value as the counter goes from 1 to 288, the ForAll and Sequence functions creates it.

hey there if you liked the post give it a thumbs up, and if it solved your question please accept it as a solution.

Announcements

Power Platform Connections Ep 14 | J. Panchal | Thursday, 18 May 2023

Episode Fourteen of Power Platform Connections sees David Warner and Hugo Bernier talk to Microsoft PM Jocelyn Panchal, alongside the latest news, videos, product reviews, and community blogs.   Use the hashtag #PowerPlatformConnects on social media for a chance to have your work featured on the show.      Show schedule in this episode:  00:00 Cold Open 00:32 Show Intro 01:10 Jocelyn Panchal Interview 24:10 Blogs & Articles 29:50 Outro & Bloopers  Check out the blogs and articles featured in this week’s episode:   https://www.nathalieleenders.com/Blog/index.php/;focus=STRATP_com_cm4all_wdn_Flatpress_42136159&path=?x=entry:entry230511-101930#C_STRATP_com_cm4all_wdn_Flatpress_42136159__-anchor  @NathLeenders https://www.keithatherton.com/posts/2023-05-12-msbuild2023-cloud-skills-challenge/  @MrKeithAtherton https://elliskarim.com/2023/05/13/how-to-find-files-in-onedrive-that-match-a-naming-pattern/  @MrCaptainKarim https://www.linkedin.com/pulse/my-fond-memories-scottish-summit-2022-pranav-khurana/ @pranavkhuranauk https://www.linkedin.com/feed/update/urn:li:activity:7061777660745560064/?updateEntityUrn=urn%3Ali%3Afs_feedUpdate%3A%28V2%2Curn%3Ali%3Aactivity%3A7061777660745560064%29  @thevictordantas  Action requested: Feel free to provide feedback on how we can make our community more inclusive and diverse.  This episode premiered live on our YouTube at 12pm PST on Thursday 18th May 2023.  Video series available at Power Platform Community YouTube channel.  Upcoming events:  Power Apps Developers Summit – May 19-20th - London European Power Platform conference – Jun. 20-22nd - Dublin Microsoft Power Platform Conference – Oct. 3-5th - Las Vegas  Join our Communities:  Power Apps Community Power Automate Community Power Virtual Agents Community Power Pages Community  If you’d like to hear from a specific community member in an upcoming recording and/or have specific questions for the Power Platform Connections team, please let us know. We will do our best to address all your requests or questions.

Microsoft Power Platform Conference | Registration Open | Oct. 3-5 2023

We are so excited to see you for the Microsoft Power Platform Conference in Las Vegas October 3-5 2023! But first, let's take a look back at some fun moments and the best community in tech from MPPC 2022 in Orlando, Florida.   Featuring guest speakers such as Charles Lamanna, Heather Cook, Julie Strauss, Nirav Shah, Ryan Cunningham, Sangya Singh, Stephen Siciliano, Hugo Bernier and many more.   Register today: https://www.powerplatformconf.com/

Check out the new Power Platform Communities Front Door Experience!

We are excited to share the ‘Power Platform Communities Front Door’ experience with you!   Front Door brings together content from all the Power Platform communities into a single place for our community members, customers and low-code, no-code enthusiasts to learn, share and engage with peers, advocates, community program managers and our product team members. There are a host of features and new capabilities now available on Power Platform Communities Front Door to make content more discoverable for all power product community users which includes ForumsUser GroupsEventsCommunity highlightsCommunity by numbersLinks to all communities Users can see top discussions from across all the Power Platform communities and easily navigate to the latest or trending posts for further interaction. Additionally, they can filter to individual products as well.       Users can filter and browse the user group events from all power platform products with feature parity to existing community user group experience and added filtering capabilities.     Users can now explore user groups on the Power Platform Front Door landing page with capability to view all products in Power Platform.    Explore Power Platform Communities Front Door today. Visit Power Platform Community Front door to easily navigate to the different product communities, view a roll up of user groups, events and forums.