How to use the round function

[hmoorex3]

My problem is a decimal is significantly affecting my formula. The six rows in question all use the same basic formula


The first problem is C6 and C7 is coverted as a decimal in the formula. That obviously affects the whole rest of the formula. I tried =+((roundup(roundup(round(C7,0)*0.41,0)/12.... but it returned an error.


It's not a issue so much for when C6 or C7 are smaller numbers, but when you get into the higher numbers(in later columns the numbers are in the hundreds) at the end of the formula because of the decimal, the number of contracts totals more than what was started with in C6 or C7 (see N9, N10, N46-N48).


What is the best way to address this?


Thank you


https://dl.dropbox.com/u/25491363/Adaptive%20spreadsheet_r3.xlsx

 

[Hui]

Looking at the first part of Cell C9

=((ROUNDUP(ROUNDUP(C6*0.41,0)*$C$73/12,0)...


You need to determine if the internal Roundup is required ROUNDUP(C6*0.41,0) ?

would C6*0.41 give an answer that can properly be processed by the rest of the formula

Becoming =ROUNDUP(C6*0.41*$C$73/12,0)...

etc


You are also rounding up all the time


Are you also aware of the Round() function?


I also notice that you aren't using a table of ratios as per previous posts? But are using fixed values

 

[NARAYANK991]

Hi Moore ,


I am not able to understand the reason for the formulae in rows 9 and 10 ; if you can detail the logic that is to be implemented , probably a simpler and more accurate formula can be written.


Secondly , since so many figures are being rounded up , can you clarify why this rounding up is required ; which of the figures really needs to be rounded up ? For example , can we have the number of contracts as 1.8 , 1.2 , 4.8 , 3.2 and so on , which is what is happening in rows 6 and 7 ? These figures are calculated from the figures in row 3 , which are all integers , and the values from an external worksheet ; what are these two values from the external worksheet ?


Thirdly , why is no use being made of the values in the range I73:L76 ?


Rounding up should be done only at two places , either at the beginning e.g. if the number of contracts is 1.8 , round it up to 2 , or at the end , when the final value after all the calculations is rounded up. Also , you need to decide what kind of rounding is appropriate ; should 1.8 become 2 , and should 1.2 also become 2 ? Or should 1.8 become 2 , while 1.2 becomes 1 ? Rounding up at every stage will clearly skew the output.


Consider a simple calculation :

[pre]

E4 = 1.8
E5 = 1.2

F4 : =E4/2
F5 : =E5/2

G4 : =F4*3.7
G5 : =F5*3.7

H4 : =ROUNDUP(G4,0)
H5 : =ROUNDUP(G5,0)
The final result in H4 will be 4 , while H5 will be 3. If you had used the ROUND function , you would have ended up with outputs of 3 and 2.


If you now ROUNDUP the initial values of 1.8 and 1.2 to zero decimal places , you end up with 2 in both cases ; the final output will be 4 and 4.


If you use the ROUND function on 1.8 , you get 2 , and using it on 1.2 results in 1 ; the final output in this case is 4 and 2.


Thus you have so many possible outcomes based on what you do :

4 and 4
4 and 3
3 and 2
4 and 2

[/pre]
The picture painted by this range of possibilities can be either : the situation has not changed in any way ( 4 and 4 ) , or there has been a dramatic change ( 4 and 2 ) ; which one is applicable , only you can decide.


What it means is , that practicality will decide which of the outputs is more indicative of the reality. Excel can be made to show anything !


Narayan

 

[hmoorex3]

I apologize for the delay in responding - to tell you the truth I simply could not get my head around either of your solutions. I did however take something from both of them to resolve my issue so thank you. Hopefully this is the last question I have: As more cities are under contract, the commission paid out gets to be too much so I want to remove contracts after they have been on the books for 18 months. For example: '2015 Expenses'! I46 would subtract '2014Expenses'!C6. J46 would subtract C6:D6, K46 would subtract C6:E6 and so on. How do I write a formula to perform the above? As you can see I started something but quickly realized I didn't know how to write it so it would keep including the months prior. (I haven't started to do anything yet with row 47). NARAYANK991, in answer to your question, I72-L76 are side computations to give me an idea of what the commission or bonus amount would be at a certain percentage point.


Thank you for your help.


https://dl.dropbox.com/u/25491363/Adaptive%20spreadsheet_r4.xlsx

 

[NARAYANK991]

Hi ,


Change your formula in I46 from :


=I6-'2014 Expenses'!C6


to


=I6-SUM('2014 Expenses'!$C$6:C6)


When you copy this across , it will change as follows :


=J6-SUM('2014 Expenses'!$C$6:D6)


=K6-SUM('2014 Expenses'!$C$6:E6)


=L6-SUM('2014 Expenses'!$C$6:F6)


and so on.


Narayan

 

[hmoorex3]

NARAYANK991, I don't know if I'm more embarrassed or proud that I arrived at the same solution upon wakening this morning. Thank you.


For month 1 of 2016 will I simply be able to copy the formula from month 12 of 2015 or will I have to modify it? This is a five year forecast so there are years '14-'18.

 

[NARAYANK991]

Hi ,


That will depend on what the logic is , and which cell you are going to copy it into.


If we assume the same logic , then N46 in the '2015 Expenses' tab will have the formula :


=N6-SUM('2014 Expenses'!$C$6:H6)


If you are now going to start the tab '2016 Expenses' from column C , then C46 should have :


=C6-SUM('2014 Expenses'!$C$6:I6)


Will this be correct ? Only you can say.


This is the problem with having separate tabs for different years ; if all the data were in one tab , just carrying on year after year , then probably you could have one formula for the starting month , and another for all the remaining months.


Narayan

 

[hmoorex3]

Ran into a problem with the 2nd valuation =J6-SUM('2014 Expenses'!$C$6:D6)

Comes back with one short. Tried to round but hit a wall everytime I changed the formula.


=J6-SUM(ROUND(('2014 Expenses'!$C$6)'2014 Expenses'!D6)),0 (and every variation thereof I could imagine)

 

[hmoorex3]

Sorry - came back one over.

 

[NARAYANK991]

Hi ,


The formula should be as follows :


=J6-ROUND(SUM('2014 Expenses'!$C$6:'2014 Expenses'!D6),0)


since the ROUND function should be outside the SUM function.


J6 evaluates to 88.2 in the file I downloaded ; since the external linked file is not available , I cannot say whether this is right or wrong ; only you can verify that.


The SUM of C6 and D6 evaluates to 6.6 , which when rounded becomes 7.


Hence the overall result is 81.2


You need to decide whether the rounding up of 6.6 to 7 is justified ; should be rounding be done at the last stage , after calculating 88.2 - 6.6 ? Using ROUND on this result will give 82 ; using the present formula will give 81.2 ; using ROUND on this will give 81.


You need to decide whether 81 is correct or 82 is correct. As I said earlier , Excel can be made to show whatever you wish.


Narayan