**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.*