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! Go to 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)
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))
User | Count |
---|---|
40 | |
34 | |
28 | |
23 | |
18 |