Adding nearest numbers to get value

[Sarvesh_Mudkavi]

Hello Community!

I was working on appsheet and I wanted to check whether its possible to get a result out of particular cell value by adding some row values to match the desired cell value. 

Please find the screenshot for better understanding. 

From the above image I'm trying to add "I" column values to match "J2" Value ( decimal value match not required ).

Thanks in advance!!

Cheers!

[Rafael_ANEIC-PY]

Hello @Sarvesh_Mudkavi, what do you mean "match the desired cell value"? what is exactly the result you want to get? an example would help hehe

[Steve]

I do not understand.

[AkashK]

Assume array [5000,1000,500,100,50].    and reference value as = 18345

expected value should be = (5000*3)+(1000*3)+(100*3) = 18300.

so the nearest value should be 18300.

he wants a formula to do this job.

[Sarvesh_Mudkavi]

Yes this is what I require. 

Thanks 😀

But I don't want it to calculate any number x 3 times,

Just 5000+1000+500+100+50, because there are only these numbers.

[Rafael_ANEIC-PY]

So for example, if the number was 9250, then the answer should be this?

1*5000+4*1000+0*500+2*100+1*50 

Because that could be done, if those are the only numbers and it prioritizes the higher ones.

[Sarvesh_Mudkavi]

Yep 👍

[Rafael_ANEIC-PY]

Well, if it is as @AkashK says, then no, there's no way you can do that on AppSheet, not like this at least, you're gonna need a lot of steps to make something like this sort of work, like preselecting which numbers should constitute the reference value and stuff like that.

[Sarvesh_Mudkavi]

Hmmm.

Need to find other ways then.

[Ryan_Mortimer]

I won’t say impossible, but I would question whether the juice is worth the squeeze.

if you could find an excel formula that achieved a similar thing, then you might be able to get a steer in the correct direction, then you will need to find a way to enter the “control” number which is actually the easy part of this.

I think the closest use case that would be common (in an excel sheet) would be to calculate a gear ratio (between two cogs) or fuel/oil ratio.

they sell hand held cards for this purpose, so surely someone has something somewhere

[Rafael_ANEIC-PY]

Okay then, this is gonna be a nasty expression, but It'll work.

First number(a*5000):

FLOOR([number]/5000)

Second number(b*1000):

FLOOR(
(
[number]-
(FLOOR([number]/5000)*5000)
)/1000)

Third number(c*500):

FLOOR(
(
[number]-FLOOR([number]/5000)*5000-FLOOR(([number]-(FLOOR([number]/5000)*5000))/1000)*1000
)/500)

Fourth number(d*100):

FLOOR(
([number]-
FLOOR([number]/5000)*5000-
FLOOR(([number]-(FLOOR([number]/5000)*5000))/1000)*1000-
FLOOR(([number]-FLOOR([number]/5000)*5000-FLOOR(([number]-(FLOOR([number]/5000)*5000))/1000)*1000)/500)*500
)/100)

Fifth number(e*50):

FLOOR(
(
[number]
-
FLOOR([number]/5000)*5000
-
FLOOR(
(
[number]-
(FLOOR([number]/5000)*5000)
)/1000)*1000
-
FLOOR(
(
[number]-FLOOR([number]/5000)*5000-FLOOR(([number]-(FLOOR([number]/5000)*5000))/1000)*1000
)/500)*500
-
FLOOR(
([number]-
FLOOR([number]/5000)*5000-
FLOOR(([number]-(FLOOR([number]/5000)*5000))/1000)*1000-
FLOOR(([number]-FLOOR([number]/5000)*5000-FLOOR(([number]-(FLOOR([number]/5000)*5000))/1000)*1000)/500)*500
)/100)*100
)/50)

AND THE CLOSEST VALUE IS:

a*5000+b*1000+c*500+d*100+e*50

AND IT IS THIS BEHEMOTH:

FLOOR([number]/5000)*5000
+
FLOOR(
(
[number]-
(FLOOR([number]/5000)*5000)
)/1000)*1000
+
FLOOR(
(
[number]-FLOOR([number]/5000)*5000-FLOOR(([number]-(FLOOR([number]/5000)*5000))/1000)*1000
)/500)*500
+
FLOOR(
([number]-
FLOOR([number]/5000)*5000-
FLOOR(([number]-(FLOOR([number]/5000)*5000))/1000)*1000-
FLOOR(([number]-FLOOR([number]/5000)*5000-FLOOR(([number]-(FLOOR([number]/5000)*5000))/1000)*1000)/500)*500
)/100)*100
+
FLOOR(
(
[number]
-
FLOOR([number]/5000)*5000
-
FLOOR(
(
[number]-
(FLOOR([number]/5000)*5000)
)/1000)*1000
-
FLOOR(
(
[number]-FLOOR([number]/5000)*5000-FLOOR(([number]-(FLOOR([number]/5000)*5000))/1000)*1000
)/500)*500
-
FLOOR(
([number]-
FLOOR([number]/5000)*5000-
FLOOR(([number]-(FLOOR([number]/5000)*5000))/1000)*1000-
FLOOR(([number]-FLOOR([number]/5000)*5000-FLOOR(([number]-(FLOOR([number]/5000)*5000))/1000)*1000)/500)*500
)/100)*100
)/50)*50

Why did I even do this? 

[Sarvesh_Mudkavi]

Wow 😵😵 that's some expression ...

Thanks for everything @Rafael_ANEIC-PY will try to implement this. 👍🙌