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

Add labels to the gallery

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

Add a label and add this code to the label

``````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.

Can you please add the 5.2 in your Mean area and 3,4 in temp 😉

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

#### Take a short Community User Survey | Help us make your experience better!

To ensure that we are providing the best possible experience for Community members, we want to hear from you!    We value your feedback! As part of our commitment to enhancing your experience, we invite you to participate in a brief 15-question survey. Your insights will help us improve our services and better serve the community.   👉 Community User Survey    Thank you for being an essential part of our community!    Power Platform Engagement Team

#### Tuesday Tip | How to Get Community Support

It's time for another Tuesday Tip, your weekly connection with the most insightful tips and tricks that empower both newcomers and veterans in the Power Platform Community! Every Tuesday, we bring you a curated selection of the finest advice, distilled from the resources and tools in the Community. Whether you’re a seasoned member or just getting started, Tuesday Tips are the perfect compass guiding you across the dynamic landscape of the Power Platform Community.       This Week: All About Community Support Whether you're a seasoned community veteran or just getting started, you may need a bit of help from time to time! If you need to share feedback with the Community Engagement team about the community or are looking for ways we can assist you with user groups, events, or something else, Community Support is the place to start.   Community Support is part of every one of our communities, accessible to all our community members.   Within each community's Community Support page, you'll find three distinct areas, each with a different focus to help you when you need support from us most. Power Apps: https://powerusers.microsoft.com/t5/Community-Support/ct-p/pa_community_support Power Automate: https://powerusers.microsoft.com/t5/Community-Support/ct-p/mpa_community_support Power Pages: https://powerusers.microsoft.com/t5/Community-Support/ct-p/mpp_community_support Copilot Studio: https://powerusers.microsoft.com/t5/Community-Support/ct-p/pva_community-support   Community Support Form If you need more assistance, you can reach out to the Community Team via the Community support form. Choose the type of support you require and fill in the form accordingly. We will respond to you promptly.    Thank you for being an active part of our community. Your contributions make a difference!   Best Regards, The Community Management Team

#### Calling all User Group Leaders and Super Users! Mark Your Calendars for the next Community Ambassador Call on May 9th!

This month's Community Ambassador call is on May 9th at 9a & 3p PDT. Please keep an eye out in your private messages and Teams channels for your invitation. There are lots of exciting updates coming to the Community, and we have some exclusive opportunities to share with you! As always, we'll also review regular updates for User Groups, Super Users, and share general information about what's going on in the Community.     Be sure to register & we hope to see all of you there!