Virtual column formula

[Phong_Lam1]

Hi all, I need to create a virtual column [factor]. When users will in the value of [the number of items]. The app will use [factor] to calculate the price base on the value of [the number of items]. It has the formula in excel as below.

But I don't know how to do that formula in Appsheet for [factor]. Thanks all

[jrullan]

Hi, I'm not sure I understand your question correctly, but can you just add a Factors table to the app and then search for the factor based on the user's entry in the other's table form?

id	number of items	factor

Assuming you are calculating the price in another table's adjusted price:

id	item	number of items	price	factor	adjusted_price

Then the factor formula would be

IF(ISNOTBLANK([number of items]),
Index(
  Select(
    Factors[factor], [number of items] = [_ThisRow].[number of items]
  ),
  1
),"")

 And the adjusted_price would be:

[price]*[factor]

[Phong_Lam1]

Hi @jrullan, thanks for your hard work. I mean I want to the value of [factor] which is a virtual column calculated based on  [number of items]  filled by user in the form. The formula of [factor] in Excel is this. For example factor L11= =L10+1*(100-K11)/100

. But For Appsheet I don't know how to calculate for [factor], I get stuck at L10. The expression for [factor] for row 11 should be L10 +1*(100-[number of items])/100 . How can we address L10 in formula for [factor] in appsheet ?

[jrullan · Accepted solution]

To clarify, do you have that table added in Appsheet?

If not, I think it is preferable to have it as a data source and then do the lookup as I described above. 

Your formula seems to be recursive, so it always depends on another value of  [number of items] (in this case for L11 depends on the calculation of L10 which in turn depends on the calculation of L9 and so for).  

Other than looking it up in a pre-calculated table added to Appsheet as a source, I don't know how to accomplish what you want.

[Phong_Lam1]

Thanks so much @jrullan. your method can also do with other components for price when these components have factors within the same table.

[SkrOYC]

Re: FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), R... - Google Cloud Community