Creating a Product Quote Calculator Help

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&row=2&table=Magnet&view=Magnet

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

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…

What is your sheet formula?

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))))))))))

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?

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
)

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?

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

1 Like

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

You’re welcome