Question description:
This user has given permission to use the problem statement for this
blog.
I need a formula that takes the sum of one cell and times every 10 by 43 and the last 5 by 22.50
Solved by O. B. in 51 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
10/11/2017 - 07:02
Hello :)
User
10/11/2017 - 07:02
Hello
Excelchat Expert
10/11/2017 - 07:03
how can I help.
Excelchat Expert
10/11/2017 - 07:03
Are you able to share your sheet?
User
10/11/2017 - 07:04
I need a formula that takes the sum of cell L59 and then times every 10 by 43 and the last 5 by 23.50. I cant share file
Excelchat Expert
10/11/2017 - 07:04
Okay, What do you mean by times every 10?
Excelchat Expert
10/11/2017 - 07:05
times every 10 by 43*
User
10/11/2017 - 07:06
so if the sum of cell L59 is 45, then it will take every 10 and times it by 43. So it would be 4*43, then the last five is 1*23.5
Excelchat Expert
10/11/2017 - 07:06
Oh, so 45/10 = 4 and then 5*23.5?
User
10/11/2017 - 07:06
yes
Excelchat Expert
10/11/2017 - 07:07
Okay, sure. I can do that for you.
Excelchat Expert
10/11/2017 - 07:07
=L59/10
Excelchat Expert
10/11/2017 - 07:08
Is it always in increments of 5?
User
10/11/2017 - 07:08
no
Excelchat Expert
10/11/2017 - 07:08
So what if it's 42?
User
10/11/2017 - 07:08
what the cell does, it rounds up to the nearest 5.
User
10/11/2017 - 07:08
so the answer will always show in 5s
Excelchat Expert
10/11/2017 - 07:08
I see.
User
10/11/2017 - 07:09
so if its 35, then there a 3, 10s in there and 1, 5.
Excelchat Expert
10/11/2017 - 07:10
I see. I'm with you. One moment please.
Excelchat Expert
10/11/2017 - 07:16
=IFL59/10=INT(L59/10),(L59/10*43)+(5*23.5),L59/10*43
User
10/11/2017 - 07:16
let me test that, I will be back
Excelchat Expert
10/11/2017 - 07:16
=IF(L59/10=INT(L59/10),(L59/10*43)+(5*23.5),L59/10*43)
Excelchat Expert
10/11/2017 - 07:16
Sorry, the parrenthisesse were deleted by accident. Use the second one.
User
10/11/2017 - 07:19
Kind off. It is mulitplying all the 5s as well. I just need it it mulitply the last 5 if there is one
Excelchat Expert
10/11/2017 - 07:20
so it would always be 5*23.5 correct>
Excelchat Expert
10/11/2017 - 07:20
?
User
10/11/2017 - 07:20
no
Excelchat Expert
10/11/2017 - 07:20
If there is a 5 I mean.
Excelchat Expert
10/11/2017 - 07:21
so 45, 35, 25, 15 will all have 5*23.5 + the cell/10
User
10/11/2017 - 07:21
if the sum is 30, then its 3*43. But if its 35, then its 3*43 and 1*23.5
Excelchat Expert
10/11/2017 - 07:21
Yes, okay, I understand that part.
User
10/11/2017 - 07:21
i dont udnerstand
User
10/11/2017 - 07:22
if the sum is 30, then its 3*43. But if its 35, then its 3*43 and 1*23.5
Excelchat Expert
10/11/2017 - 07:22
I think that's what the formula I sent is doing.
Excelchat Expert
10/11/2017 - 07:22
It is only multiplying one 5 by 23.5
Excelchat Expert
10/11/2017 - 07:22
and then dividing the cell by 10.
User
10/11/2017 - 07:22
yes
User
10/11/2017 - 07:22
yes
Excelchat Expert
10/11/2017 - 07:23
Okay, so my formula works then?
User
10/11/2017 - 07:23
no.
Excelchat Expert
10/11/2017 - 07:23
Okay, this is what the formula I sent is doing right now.
Excelchat Expert
10/11/2017 - 07:24
It is checking for L59/10. If L59/10 is not a whole number, it is adding 5*23.5 to L59/10*43.
User
10/11/2017 - 07:24
because it is doing all the 5s in 35. It does not need to do that because the forst 10 is already done by 4
User
10/11/2017 - 07:24
43
User
10/11/2017 - 07:24
the last and only 5, if there is a 5, needs to be done 23.5
Excelchat Expert
10/11/2017 - 07:24
One moment lease.
Excelchat Expert
10/11/2017 - 07:24
please
Excelchat Expert
10/11/2017 - 07:27
Okay, so 4*43 = 172
Excelchat Expert
10/11/2017 - 07:28
and 5*23.5 = 117.5
Excelchat Expert
10/11/2017 - 07:28
Therefore, the answer is 289.5
Excelchat Expert
10/11/2017 - 07:28
for 45 correct?
User
10/11/2017 - 07:28
no, 5 comes only once
User
10/11/2017 - 07:28
4*43
User
10/11/2017 - 07:28
1*23.5
Excelchat Expert
10/11/2017 - 07:29
Oh, I see. easy fix.
Excelchat Expert
10/11/2017 - 07:29
=IF(L59/10=INT(L59/10),(L59/10*43)+(1*23.5),L59/10*43)
Excelchat Expert
10/11/2017 - 07:29
Please try that.
User
10/11/2017 - 07:29
but, if its 40, then its just 4*43
Excelchat Expert
10/11/2017 - 07:29
Yup, I'm with you.
User
10/11/2017 - 07:31
You fomula is wrong. It adds 23.5 when its just 40. It only needs to add 23.5 if its 45 or 55 or 65 etc.
Excelchat Expert
10/11/2017 - 07:33
Yes, I noticed it is reversed.
Excelchat Expert
10/11/2017 - 07:33
One moment and I will correct. Sorry.
User
10/11/2017 - 07:33
ok, please hurry, I am out of credits
Excelchat Expert
10/11/2017 - 07:34
=IF(L59/10=INT(L59/10),L59/10*43,(L59/10*43)+(1*23.5))
User
10/11/2017 - 07:35
testing. Be right back
Excelchat Expert
10/11/2017 - 07:35
Okay, thank you.
User
10/11/2017 - 07:36
its wrong again
Excelchat Expert
10/11/2017 - 07:36
=IF(J7/10=INT(J7/10),((J7/10)*43),(J7/10)*43+(1*23.5))
Excelchat Expert
10/11/2017 - 07:36
I just tested this formula on my machine.
Excelchat Expert
10/11/2017 - 07:36
The only difference is the cell reference.
Excelchat Expert
10/11/2017 - 07:37
=IF(L59/10=INT(L59/10),((L59/10)*43),(L59/10)*43+(1*23.5))
User
10/11/2017 - 07:38
its giving me a wrong result
Excelchat Expert
10/11/2017 - 07:38
30 should be: 129
Excelchat Expert
10/11/2017 - 07:38
right?
Excelchat Expert
10/11/2017 - 07:39
35 should be 174
User
10/11/2017 - 07:39
no
User
10/11/2017 - 07:39
35 should be 152.5
User
10/11/2017 - 07:41
remember, for every 10 its 43, then IF there is a 5, then it adds 23.5
Excelchat Expert
10/11/2017 - 07:42
Yes, I think it is a rounding issue.
Excelchat Expert
10/11/2017 - 07:46
I'm working on the rounding issue now.
Excelchat Expert
10/11/2017 - 07:48
=IF(J7/10=INT(J7/10),ROUNDDOWN(J7/10,0)*43,ROUNDDOWN(J7/10,0)*43+23.5
Excelchat Expert
10/11/2017 - 07:48
Okay, I got it. Just need to change the cell reference.
User
10/11/2017 - 07:48
k, let me test
Excelchat Expert
10/11/2017 - 07:48
=IF(L59/10=INT(L59/10),ROUNDDOWN(L59/10,0)*43,ROUNDDOWN(L59/10,0)*43+23.5)
Excelchat Expert
10/11/2017 - 07:48
Use that one please.
User
10/11/2017 - 07:52
It works
User
10/11/2017 - 07:52
Well done and thank you
Excelchat Expert
10/11/2017 - 07:52
Okay, great!
Excelchat Expert
10/11/2017 - 07:52
Sorry for the back and forth.
Excelchat Expert
10/11/2017 - 07:52
I'm glad we were able to solve it though :)
User
10/11/2017 - 07:52
no. You did well. It was a matter of understanding the issue
Excelchat Expert
10/11/2017 - 07:52
Is there anything else I can help with?
User
10/11/2017 - 07:53
no, thank you
Excelchat Expert
10/11/2017 - 07:53
You're very welcome. Have a great day!
User
10/11/2017 - 07:53
u 2
Excelchat Expert
10/11/2017 - 07:53
Thank you.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
