Mirroring simple excel sheet in Powerapps

[mr-samd]

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

[rubin_boer]

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(
    colTemp2,AddColumns(colTemp,"mean2", Round(Average(varMean,Average(colTemp,_temp)),2))
)

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 

[mr-samd]

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

[rubin_boer]

@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

[mr-samd]

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?

[rubin_boer]

@mr-samd 

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

[mr-samd]

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

[rubin_boer · Accepted solution]

@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

[mr-samd]

@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

[rubin_boer]

@mr-samd 

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.