Excel - SUM Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.