Recreate this google sheet formula in appsheet?

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.

Solved Solved
0 5 509
1 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)

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

  1. 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

View solution in original post

5 REPLIES 5
Top Labels in this Space