Excel Formula Question / Math Question?

[ctackett6407]

Greetings Folks,


I just can't think of how to make this work, I don't know if I'm just mentally exhausted by working on a dashboard all day, but I just can't figure out how to do this.

I have 12 columns for Months.. January - December. I'm trying to take a number and I would typically divide it by 12; however, January is half a month.

Anyone know how to make a formula that will correctly distribute the numbers?

=roundup((E15/???)) then I drag it from January to December so when I update e15 it updates the whole year?

I was going to do 11.5, but that makes it even across the board.. it should be half the amount of the remaining 11 months.

I just can't figure it out so any help would be great.. it's probably basic math and I'm overthinking it.


for example

I have E15 which has 5000 in it.

I want to distribute that number between 12 months; however, January is only 15 days.

 

[shg]

Jan is 1/23 of the total and the rest are 2/23.

 

[ctackett6407]

Jan is 1/23 of the total and the rest are 2/23.

I'll have to try that.

I tried the formula =roundup(($R$17/1/23),0)

And put 4034 in R17

I got 176 for January but when I changed it to 2/23 for the other months it was 88. Which it made a total 1144. So I'm doing something wrong.

 

[jtakw]

Hi,

It is:

=ROUNDUP(R17*1/23,0)

and

=ROUNDUP(R17*2/23,0)

Not divide "/"

But you'll still find that the Total will be "off" since you're Rounding...

 

[ctackett6407]

Thanks, this worked.

 

[ctackett6407]

Thank you both for this.

I just had 1 other question.

Why 1/23 ? I know some folks like to just ask questions and get the answer, but I like learning why I did something so in the future I can not ask the questions.

Thanks again for any help.

 

[KolGuyXcel]

Thank you both for this.

I just had 1 other question.

Why 1/23 ? I know some folks like to just ask questions and get the answer, but I like learning why I did something so in the future I can not ask the questions.

Thanks again for any help.

Divide the entire year in units of 15 days! This gives us total 24 units in a year, 2 units each month. And since we have to use only 15 days for the month of January, i.e. only 1 unit for January, the total units used = 23.