Recreate this google sheet formula in appsheet?

[SKETCHwade]

Hello,

I have this expression in google sheets:

=IFS(M2 = “”, “”,
GCD(M2,2000)=2000,2000,
GCD(M2,1000)=1000,1000,
GCD(M2,500)=500,500,
GCD(M2,250)=250,250)

For those unsure about what this formula is actually doing ill break it down.
If this spreadsheet cell is blank then return blank.
if the cell is divisible by the number next to it (in the formula) then return that number.
The first to return a true result is the one used.

To further illustrate the formula here is some test data and the following result:
Cell = 12000, 2000
Cell = 1000, 1000
Cell = 6500, 500
Cell = 7250, 250

Looking up Greatest Common Divisor on Wikipedia provides a reasonably complex mathematical algorithm to work this out but I seem unable to work this out on my own.
Any help is much appreciated.

[Suvrutt_Gurjar]

I am sure, other community members may have their own and better guiding inputs. As per my understanding finding GCD of any two numbers can be an iterative process. It can extend to many iterations in certain number combinations.

As such I believe the AppSheet expression can become very long for certain random combinations. Each number combination for which we wish to find GCD will have different number of iterations.

I believe we may explore to see if it can be simplified with following consideraions.

The example given by you is probably simple one. Could you please add

1. If the second number in your GCD() function will always be fixed group of numbers, as you have given in test example (2000, 1000, 500, 250 ) or they will vary.
2. Also the first number in your given text example matches at least one of the 4 options(2000, 1000, 500, 250 ), in real scenario will there be some numbers that do not match any four?

If the answers to 1) is the number combination will not vary and 2) is there will not be other numbers , then I believe we could attempt to come up with an AppSheet expression.

[Suvrutt_Gurjar]

I will request you to respond to queries in the above post , if you are looking for a more wide ranging use of the GCD() function. In the meantime, based on your statment below,

SKETCHwade:

if the cell is divisible by the number next to it (in the formula) then return that number.

if you are simply looking for a number being dvisible by a series of numbers in the descending order as in your example (2000, 1000,500,250) , I believe you could also try an IFS() expression something like below with MOD() function.

IFS([Quantity] = “”, “”,
MOD([Quantity] ,2000)=0,2000,
MOD([Quantity] ,1000)=0,1000,
MOD([Quantity] ,500)=0,500,
MOD([Quantity] ,250)=0,250)

Where [Quantity] is the number that the app is trying to find , if it is divisible by second argument in the MOD() expression, namely 2000, 1000, 500 and 250. Also expression will need additional logic to handle any numbers that are not divisible by any of 2000, 1000, 500 or 250

[SKETCHwade]

Hi @Suvrutt_Gurjar

Thank you very much for your thoughtful responses!

The test data I provided in my original question was poorly created.
The incoming data is machine hours so it could be a random number under ~30000
This original number we will call MachineHours
MachineHours needs to be rounded up to the nearest 250. This equals HoursWhenDue

(My Google Sheet Formula)
CEILING(MachineHours+1,250) = HoursWhenDue

Now HoursWhenDue is used to find out how large the service is. The services are completed every 250 hours. If the hours on the machine divisible by 4000 then the machine is due for a 4000 hour service. If the machine hours are divisible by 2000 the the machine is due for a 2000 hour service etc. down to a 250 hour service.

(My Google Sheet Formula)
=IFS(HoursWhenDue = “”, “”,
GCD(HoursWhenDue,2000)=2000,2000,
GCD(HoursWhenDue,1000)=1000,1000,
GCD(HoursWhenDue,500)=500,500,
GCD(HoursWhenDue,250)=250,250)
= ServiceSize

I am basically looking for a way to do this calculation within appsheet instead of google sheets.

[Suvrutt_Gurjar · Accepted solution]

Thank you @SKETCHwade for more details.These certainly help.

1)Please try in the [HoursWhenDue] column’ s app formula

250*CEILING(([MachineHours]+1)/250.00)

2. Please try in the [ServiceSize] column’s app formula

IF(ISBLANK([HourWhenDue]), NUMBER(" "),IFS(
MOD([HourWhenDue],2000)=0,2000,
MOD([HourWhenDue] ,1000)=0,1000,
MOD([HourWhenDue] ,500)=0,500,
MOD([HourWhenDue] ,250)=0,250))

3. The test below shows [MachineHours] further computed to give HoursWhenDue and “ServiceSize” computations. In Green with AppSheet formulas and in orange with Google spreadsheet formulas shared by you

[SKETCHwade]

Thank you @Suvrutt_Gurjar

I really appreciate the amount of effort you have put in here!