Complex SUM with ROUNDing - all in one cell possible?

Hi there,

I am an experienced Excel user, but it does not seem possible for this formula to be written all within one single cell. The SUM combines rounding a known value, followed by calculations, and subtracting a rounded number with modified calculations.


This is the formula which does work, although I would prefer for the sake of accuracy (and succeeding in the task of making the formula do exactly what I wanted it to do!!):

=ROUND(SUM(T9/1.5)/5,0)*(5)*(6.5%)+(26.25+15.81+1)+SUM((T9/1.5)*(6.5%)+26.25+15.81+1)*19%

The value of T9 is 2283.3 recurring.


There are two main parts to the SUM:

=ROUND(SUM(T9/1.5)/5,0)*(5)*(6.5%)+(26.25+15.81+1)

...and:

+SUM((T9/1.5)*(6.5%)+26.25+15.81+1)*19%


The challenge is to insert another:

=ROUND(SUM(T9/1.5)/5,0)*(5)

in replacement of:

=SUM((T9/1.5)


I'm aware that the use of brackets differs between the two sections of the sum, but it is neccessary to allow the correct answer.

Is this too much for one poor cell to handle?

Kind Regards,
Dale

Hi,

Can you attach an example and indicate what value you expect to see returned by the formula.

Rgds

How about =(ROUND( T9 / 1.5 / 5, 0) * 5 * 6.5% + 26.25 + 15.81 + 1) * 119%

or just =(ROUND( T9 / 7.5, 0) * 0.325 + 43.06) * 119%

You have way more parens than you need. My formula was responsive to your original request to

insert another:

=ROUND(SUM(T9/1.5)/5,0)*(5)

in replacement of:

=SUM((T9/1.5)

.. which I believe it does.

I am assuming you are trying to round to the nearest 5 on your figures!

so you had an error in your second half of the formula, and the sum statements are unnecessary :-

Please Login or Register  to view this content.

This gives the correct result!
Which is the same as :-

Please Login or Register  to view this content.

which both give the result 168.8134

adding

Please Login or Register  to view this content.

Gives exactly 168.81 which is what you wanted!

Would you please mark the thread as Solved?

Click the Edit button on your first post in the thread

Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes