Excel-esque text input to do quick calculations

[JMerk]

Hey there, kind of a weird one.

I've got a subset of users that, in order to begin doing what they need, take various measurements of product and then do addition and subtraction to get a net result.  They currently use Excel for a lot of their data gathering but we feel that Power Apps is significantly better to gather data and keep it all organized without having a million other excel files that ties the stuff together. 

In short: Is there a way I can use a text box to do simple math?  User types in box: 10+10+10-5-5 and then when they click a button or just click off the box it performs the math.

Thanks 🙂 

I hope my explanation was clear enough haha

[mmollet]

This is definately possible! Here is an example of a tax calculator I build that is accurate for single people filing single and claiming no dependents. I am working on the dependents and married etc but that is much more involved. This took me about 2 hours to build for reference. 

I think you can either have multiple boxes that all get added/subtracted as needed or you can use Split function to figure out what is going on although that seems much harder in my opinion. 

I would try just making a column of text inputs with a title of Add and another with a title of subtract then at the bottom of the page just sum all the values. Should be very simple and quick app and should be easy to use as well. If you want to store the data and expand on it then you can do so as well!

[mmollet · Accepted solution]

Here is a working example for you to use to visual ideas as well:

[JMerk]

Oh that's a good idea!

Was hoping for an excel-like cell but it's looking like that might not be possible.  I can definitely work with this.

Thank you for your help 🙂

[TheRobRush]

IF it is always going to be addition and subtraction you can do this.

in a button onselect

Clear(placeQueue);

ClearCollect(mathIsFun,

ForAll(

RemoveIf(
ForAll(
Split(TextInput3,"") As mathematicalFormula,
   Collect(placeQueue,{space:mathematicalFormula.Value});
   {Type: CountRows(placeQueue), Value: mathematicalFormula.Value, Place:CountRows(placeQueue)};
), !IsNumeric(Value)) As step2,

{Operator: Value(Concatenate(Last(FirstN(placeQueue,step2.Place-1)).space,step2.Value))}


));

UpdateContext({endSum:Sum(mathIsFun,Operator)})

then just put endSum in a label and voila.

if it is going to need multiplaction and division it will get more complicated

[TheRobRush]

can also throw it in the onchange of the text input but you have to press enter when you want it to update, or click outside the text input

[JMerk]

Thanks for the idea!

I've not used a good chunk of those functions myself so I've got to learn about what's going on.  

At the moment, if I type "100+100" I get 22 for the result. If the numbers being added are a single digit it seems to handle it just fine but the moment it gets above 9 it starts to run into issues.  9+9 is 18, but 9+10 is 20.

Edit: Instead of a "" in the split, I added a space and after the number, as long as there is a space it adds it correctly.  I'll just have to make sure to communicate that to my users.

[TheRobRush]

Haha oh yah that would be an issue, give me a second I will rewrite that, I now why it is occuring

[TheRobRush]

here you go, sorry it took a while, was trying to work out * x X and / as well, but that will have to be done another day haha makign progress but it is slow.

in the meantime this will handle addition and subtraction, now with multipl digits

ClearCollect(mathIsFun,
LastN(
ForAll(
Split(TextInput3.Text,"+") As addPass, 
    If(
    IsNumeric(addPass.Value),
    {Value:addPass.Value, Operator:"+"},
    {Value:Last(Sort(RemoveIf(ForAll(
                    Sequence(Len(addPass.Value)) As stepDown,
                    {Value:If(IsNumeric(Left(addPass.Value, stepDown.Value)),Left(addPass.Value, stepDown.Value))}),IsBlank(Value)),Value,SortOrder.Ascending)).Value
    
    , Operator:"+"}
    )),

CountRows(
ForAll(
Split(TextInput3.Text,"+") As addPass, 
    If(
    IsNumeric(addPass.Value),
    {Value:addPass.Value, Operator:"+"},
    {Value:Last(Sort(RemoveIf(ForAll(
                    Sequence(Len(addPass.Value)) As stepDown,
                    {Value:If(IsNumeric(Left(addPass.Value, stepDown.Value)),Left(addPass.Value, stepDown.Value))}),IsBlank(Value)),Value,SortOrder.Ascending)).Value
    
    , Operator:"+"}
    )
    )
    )-1
    )
    );

Collect(mathIsFun,

LastN(
    
ForAll(
Split(TextInput3.Text,"-") As addPass, 
    If(
    IsNumeric(addPass.Value),
    {Value:addPass.Value, Operator:"-"},
    {Value:Last(Sort(RemoveIf(ForAll(
                    Sequence(Len(addPass.Value)) As stepDown,
                    {Value:If(IsNumeric(Left(addPass.Value, stepDown.Value)),Left(addPass.Value, stepDown.Value))}),IsBlank(Value)),Value,SortOrder.Ascending)).Value
    
    , Operator:"-"}
    )),

CountRows(
    ForAll(
Split(TextInput3.Text,"-") As addPass, 
    If(
    IsNumeric(addPass.Value),
    {Value:addPass.Value, Operator:"-"},
    {Value:Last(Sort(RemoveIf(ForAll(
                    Sequence(Len(addPass.Value)) As stepDown,
                    {Value:If(IsNumeric(Left(addPass.Value, stepDown.Value)),Left(addPass.Value, stepDown.Value))}),IsBlank(Value)),Value,SortOrder.Ascending)).Value
    
    , Operator:"-"}
    )))-1
    
    
    ));

UpdateContext({endSum:Sum(
Sum(Filter(mathIsFun,Operator="+"),Value),
Sum(Filter(mathIsFun,Operator="-"),Value)*-1
)})

[TheRobRush]

Actually I forgot to add the very first number of the series into the final sum, and just left the office, will update that code tomorrow morning so its here if anyone ever needs that functionality, had to drop it in both tables that power the math, but meant to add it back a5 end >.<