Hello, please help me with this one:
Excel should find first number smaller than input number, and to be divisible with some specified number.
For example, Cell A1 number 212
For example, Cell B1 number 20
C1 should show result 200 (first number smaller than 212 and divisible with 20)
Thanks in advance
How aboutFormula:
Please Login or Register to view this content.
Use this:
=A1-SUMPRODUCT(--(MOD((A1-ROW(1:25)),20)=0)*ROW(1:25))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
That is just great, thank you very much!
I love this forum already
I tried first formula and it works. Is there any difference and benefit to use second one?
None. But check witg a few other mumbers. I now think both fail. Im out looking at carpets now. Back later.
Should it not be this?
=INT(A1/A2)*A2
Excel 2016 (Windows) 32 bit
Sheet: Sheet1
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Wondering why nobody has suggestedFormula:Please Login or Register to view this content.
Because nobody thought of it ... Until now!!!
Or perhaps simply because we like to overcomplicate things
No, I can honestly say that I hadn't thought of it. My menopausal brain doesn't remember these nuggets unless I write them down.
Correct me if I'm wrong, but I believe the FLOOR function is calculating what I suggested, isn't it?
If you have A1=200 & B=20, both
=INT(A1/B1)*B1
and
=FLOOR(A1,B1)
will return 200, but the OP wantedSo using Jason's idea it would befirst number smaller than input number, and to be divisible with some specified number
=FLOOR(A1-1,B1)
Thanks all.
I checked first formula:=INT((A1-1)/B1)*B1, in many different cases and it seems to work. I already implemented it in my work so won't change, I suppose....
I was wondering about that, Fluff, but went with what I thought was more likely to be correct rather than looking at the question as literal.
You're welcome & thanks for the feedback.Originally Posted by beard
@Jason
I know what you mean & your idea was definitely the simpler solution.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
I just overcomplicated it... as usual... and got it incorrect at the same time!!
However, do check out Fluff13's formula with 200 (or any multiple of 20) as the input number. It fails and falls over. Use FLOOR or Ali's suggestion.
I checked Fluff13 formula with 200 and 20 and result is 180, which is ok. Am I missing something?
No. You're not. Everyone else misread your request. Time I was put down, I think....
How is 180 correct? You said this:
You then told us that the answer you wanted with 212 and 20 as the inputs was 200. This is not 180. 180 is not the first number smaller than 212 to meet your criteria.Excel should find first number smaller than input number, and to be divisible with some specified number.
I read what was there.Everyone else misread your request.
Read Post 16-17, Ali
I have read posts #16 and #17 again and am none the wiser.
The input number is 212.
The divisor is 20.
200 is divisible by 20 and smaller than 212.
180 is divisible by 20 and smaller than 212, but also smaller than 200.
Therefore 200 is the first number smaller than the input number (212) that is divisible by 20.
200 is NOT the input number - it's the result. It is also the result that the OP specifically said they were looking for in the opening post.
If that's not what the OP meant, then they did not express themselves very well at all.
If the input number is 200 & the divisor is 20, the OP wanted 180 returned, rather than 200
How did you work that out? Here's what he said:
Input: 212For example, Cell A1 number 212
For example, Cell B1 number 20
C1 should show result 200 (first number smaller than 212 and divisible with 20)
Input: 20
Desired result: 200
Glenn was making the point that IF the input was 200 my formula would return 180 not 200.
I am completely confused, probably because of this in post #17:
Never mind - I give in!I checked Fluff13 formula with 200 and 20 and result is 180, which is ok.
My English is bad, and so my terminology, sorry.
Hope this shows what I wanted and got:
Capture.JPG
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks