Creating a Product Quote Calculator Help

[James_Campbell]

I confess I have no idea what I’m doing. I’ve created a Google sheet with a simple formula. There are inputs for height, width and quantity. Then, in the cell next to those inputs, there is a formula that calculates a price based on the inputs.

I would like to use Appsheet to create a nice UI when using this formula, but I can’t seem to make it work right. After I update the inputs from my App, the price doesn’t change automatically.

Could someone take a look and let me know what you guys might do to make this work correctly?

Here’s a link to it: https://www.appsheet.com/start/def42158-f337-45f3-a588-44848a99dfab#appName=Quote-1251152&page=form&...

[Aleksi]

If you want to see the calculation/result while modifying the record, you should use app formula instead of sheet formula.

[James_Campbell]

Thanks Aleksi,

Sounds like I need to learn a bit more about how Appsheet works. I’m not sure I know the difference between the two. My guess is that app formula is building the formula in Appsheet? I have concerns about that because my sheets formulas can get quite complicated…

[Aleksi]

What is your sheet formula?

[James_Campbell]

Well the formula from Google sheets is this:

=if(and(((A3B3)C3)/144>0,((A3B3)C3)/144<0.1),mround(A3B30.32,0.25),
if(and(((A3B3)C3)/144>0.100001,((A3B3)C3)/144<0.15),mround(A3B30.3,0.25),
if(and(((A3B3)C3)/144>0.150001,((A3B3)C3)/144<0.2),mround(A3B30.28,0.25),
if(and(((A3B3)C3)/144>0.20001,((A3B3)C3)/144<0.25),mround(A3B30.26,0.25),
if(and(((A3B3)C3)/144>0.250001,((A3B3)C3)/144<0.3),mround(A3B30.24,0.25),
if(and(((A3B3)C3)/144>0.30001,((A3B3)C3)/144<0.35),mround(A3B30.22,0.25),
if(and(((A3B3)C3)/144>0.350001,((A3B3)C3)/144<0.4),mround(A3B30.2,0.25),
if(and(((A3B3)C3)/144>0.40001,((A3B3)C3)/144<0.45),mround(A3B30.18,0.25),
if(and(((A3B3)C3)/144>0.450001,((A3B3)C3)/144<0.55),mround(A3B30.18,0.25),
if(and(((A3B3)C3)/144>0.550001,((A3B3)C3)/144<1000.55),mround(A3B30.15,0.25),0))))))))))

Edit: Looks like the forum takes out the multiply symbols… See below for image of code.

Edit 2: Found this burried in Appsheet. Looks like Appsheet does a conversion and applies it at some point.

if(and(((RC[-3]*RC[-2])*RC[-1])/144>0,((RC[-3]*RC[-2])*RC[-1])/144<0.1),mround(RC[-3]*RC[-2]*0.32,0.25),if(and(((RC[-3]*RC[-2])*RC[-1])/144>0.100001,((RC[-3]*RC[-2])*RC[-1])/144<0.15),mround(RC[-3]*RC[-2]*0.3,0.25),if(and(((RC[-3]*RC[-2])*RC[-1])/144>0.150001,((RC[-3]*RC[-2])*RC[-1])/144<0.2),mround(RC[-3]*RC[-2]*0.28,0.25),if(and(((RC[-3]*RC[-2])*RC[-1])/144>0.20001,((RC[-3]*RC[-2])*RC[-1])/144<0.25),mround(RC[-3]*RC[-2]*0.26,0.25),if(and(((RC[-3]*RC[-2])*RC[-1])/144>0.250001,((RC[-3]*RC[-2])*RC[-1])/144<0.3),mround(RC[-3]*RC[-2]*0.24,0.25),if(and(((RC[-3]*RC[-2])*RC[-1])/144>0.30001,((RC[-3]*RC[-2])*RC[-1])/144<0.35),mround(RC[-3]*RC[-2]*0.22,0.25),if(and(((RC[-3]*RC[-2])*RC[-1])/144>0.350001,((RC[-3]*RC[-2])*RC[-1])/144<0.4),mround(RC[-3]*RC[-2]*0.2,0.25),if(and(((RC[-3]*RC[-2])*RC[-1])/144>0.40001,((RC[-3]*RC[-2])*RC[-1])/144<0.45),mround(RC[-3]*RC[-2]*0.18,0.25),if(and(((RC[-3]*RC[-2])*RC[-1])/144>0.450001,((RC[-3]*RC[-2])*RC[-1])/144<0.55),mround(RC[-3]*RC[-2]*0.18,0.25),if(and(((RC[-3]*RC[-2])*RC[-1])/144>0.550001,((RC[-3]*RC[-2])*RC[-1])/144<1000.55),mround(RC[-3]*RC[-2]*0.15,0.25),0))))))))))

[James_Campbell]

I could also see a possibility of inputting the dimensions and quantity on one page, then clicking save and having the next page show the quote… Does that make sense?

[Aleksi]

You could use something like…

IFS(
AND([ColA] * [ColB] * [ColC]/144>=0.55,[ColA] * [ColB] * [ColC]/144<1000.55),ROUND([ColA] * [ColB] * 0.15 * 4)/4.00,
[ColA] * [ColB] * [ColC]/144>=0.40,ROUND([ColA] * [ColB] * 0.18 * 4)/4.00,
[ColA] * [ColB] * [ColC]/144>=0.35,ROUND([ColA] * [ColB] * 0.20 * 4)/4.00,
etc…
TRUE,0
)

[James_Campbell]

So I could just put something like that in the “= formula” section for that row in Appsheet and it would auto update whenever a value is changed?

[Aleksi]

Yes, add it into “App formula”. It will update the value every time you modify the record with the app.

[James_Campbell]

Great, I’ll give that a shot. Thank you Aleksi.

[Aleksi]

You’re welcome