Combining a list of formulas into one field including checking for 3rd decimal place

[Engineer445]

Hi,

I am trying to incorporate all of the below into one field. This basically does a sum, multiplies by 13.8% and then checks if the 3rd decimal place is >=6, if it is then round up or else round down. It does the same for the second part of the formula and then adds the two results together to get the final answer.

Example:
1. 5000-1548 = 3452
2. 3452 * 13.8% = 476.376
3. The 3rd decimal is >=6 so round up
4. = 476.38 --> Store this value
5. 900-89 = 811
6. 811 * 13.8% = 111.918
7. The 3rd decimal is >=6 so round up
8. = 111.92 --> Store this value
9. Add amounts from step 4 and step 8 together (476.38 + 111.92)
10. = 588.30

The problem I am having is using steps 3 and 7 within one formula. Attached is an example of the above but if I can get it all in one field i.e column C13 that would be easier.

Note the command to get the right most digit is =VALUE(RIGHT(476.376,1))

Any help would be greatly appreciated.

Example.PNG

Thanks

[AliGW]

Welcome to the forum.

=ROUND(((13.8/100)*(5000-1548)),2)+ROUND(((13.8/100)*(5000-4189)),2)

or this:

=IF(VALUE(RIGHT(((13.8/100)*(5000-1548)),1))>6,ROUNDUP(((13.8/100)*(5000-1548)),2),ROUNDDOWN(((13.8/100)*(5000-1548)),2))+IF(VALUE(RIGHT(((13.8/100)*(5000-4189)),1))>6,ROUNDUP(((13.8/100)*(5000-4189)),2),ROUNDdwn(((13.8/100)*(5000-4189)),2))

[JohnTopley]

Deleted ....

[Engineer445]

Thank you for this, if the amounts in step 5 and step 10 contain numbers that are <=6 will this still work with that you have provided?

i.e if it can do that "check for 3rd decimal" within the formula itself so it works out when to round up or round down that would be fantastic.

Sorry I replied before the post was updated! ignore this i'll try the above and see if that works.

[AliGW]

My second formula does all of that.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Engineer445]

Hi I just had to tweak it a bit because the 3rd decimal is a greater than or equal to so I ended up with this:

=IF(VALUE(RIGHT(((13.8/100)*(5000-1548)),1))>=6,ROUNDUP(((13.8/100)*(5000-1548)),2),ROUNDDOWN(((13.8/100)*(5000-1548)),2))+IF(VALUE(RIGHT(((13.8/100)*(5000-4189)),1))>=6,ROUNDUP(((13.8/100)*(5000-4189)),2),ROUNDdwn(((13.8/100)*(5000-4189)),2))

This seems to work! Many thanks for your response.

I have already added a reputation start to your name and will resolve this thread, unless you think me adding the > sign is not good practice within the above formula?

Thanks

[AliGW]

No, that's fine - should work for you.

Thanks for the rep.

[Engineer445]

Thank you. closing this thread now

[Engineer445]

Hi its me again,

When using different numbers and percentages this seems to fall off a bit and end up being out by 1. Is there a way I can truncate to 3 decimals first before doing the rest of the formula?

Example:

1. 4189-797 = 3392
2. 3392 * 5.85% = 198.432
3. The 3rd decimal is not >=6 so round down
4. = 198.43 --> Store this value
5. 4189.29-4189 = 0.29
6. 0.29 * 2% = 0.0058
7. Truncate this to 3 decimal places = 0.005
8. The 3rd decimal is not >=6 so round down
9. = 0.00 --> Store this value
10. Add amounts from step 4 and step 9 together (198.43 + 0.00)
11. = 198.43

At steps 2 and 6 whatever the result is I need to get it to truncate to at least 3dp if it is shown as more than 3dp if that makes sense?

[Engineer445]

On steps 2 and 6 I would probably need to add another IF to check if the figure is greater than 3dp in order to truncate im guessing?

[JohnTopley]

Try

=IF(--RIGHT(($A1-$B1)*C1,1)>=6,ROUNDUP(($A1-$B1)*$C1,2),ROUNDDOWN(($A1-$B1)*C1,2))+IF(--RIGHT(ROUNDDOWN(($D1-$E1)*$F1,3),1)>=6,ROUNDUP(ROUNDDOWN(($D1-$E1)*$F1,3),2),ROUNDDOWN(ROUNDDOWN(($D1-$E1)*$F1,3),2))

See attached

[Engineer445]

Thanks for the above

I can see you have added the ",3" in there to the latter part of the formula. Is there a way we can make this more transparent that whenever multiplying by the percentages if it does come in a figure that has more then 3dp then apply the truncation.

For example can we put the ",3" in both parts so that whatever numbers are used when multiplying by the percentages it will always either truncate to 3dp if its greater than 3dp or not?

Thanks in advance!

[JohnTopley]

Why not try to change it yourself?

Any calculation is likely to have more than 3 dp: formatting only shows dp that are specified.

[bebo021999]

Instead of ROUNDUP then ROUNDDOWN, I suggest to add 0.004001, then rounddown (using FLOOR):

198.432+0.004001 = 198.436001 => 198.43
198.59 + 0.004001 = 198.594001 = > 198.59
198.60 + 0.004001 = 198.604001 = > 198.60
0.0058 + 0.004001 = 0.009801 = > 0

Final formula:

Please Login or Register  to view this content.

[Engineer445]

Thanks for the responses will try them now.

Does the Floor command just truncate to 2dp?

[Engineer445]

I tried to add the ,3 in at the first part but it didnt work. Must be missing something:

=IF(--RIGHT((($A1-$B1)*C1,3),1)>=6,ROUNDUP((($A1-$B1)*$C1,3),2),ROUNDDOWN((($A1-$B1)*C1,3),2))
+
IF(--RIGHT(ROUNDDOWN(($D1-$E1)*$F1,3),1)>=6,ROUNDUP(ROUNDDOWN(($D1-$E1)*$F1,3),2),ROUNDDOWN(ROUNDDOWN(($D1-$E1)*$F1,3),2))

[Engineer445]

Got it working eventually! I didnt go for the floor method as still not sure what the 004001 equates to sorry.

final formula, Had to plug in the numbers to be sure first:

=
IF(
--RIGHT(ROUNDDOWN((4189-797)*C1,3),1)
>=6,
ROUNDUP(ROUNDDOWN((4189-797)*C1,3),2),
ROUNDDOWN(ROUNDDOWN((4189-797)*C1,3),2)
)
+
IF(
--RIGHT(ROUNDDOWN((4189.29-4189)*F1,3),1)
>=6,
ROUNDUP(ROUNDDOWN((4189.29-4189)*F1,3),2),
ROUNDDOWN(ROUNDDOWN((4189.29-4189)*F1,3),2)
)

[Engineer445]

Marking as solved again! Thank you everyone.

[bebo021999]

Does the Floor command just truncate to 2dp?

FLOOR(X,0.01) is rounddown number X to nearest 0.01
I am sure my formula works. Test it.

[Engineer445]

Sorry it's me again some years later with the same issue however the examples above seem to still be a penny out in excel and I am struggling to see where.

See the attached

The amount I am looking for is 522.31 but I am getting 522.30

It is not recognizing the 2rd digit being 0.006 and not doing the rounding up

I need it to calculate as per "example 1" but it is calculating as per "example 2" and being a penny out.
Attachment 884751

Attachment 884752


The formula for example 2 was the one listed earlier in this forum but doesnt seem to work with these particular numbers:

=
IF(--RIGHT(ROUNDDOWN(G15*(E15-F15),3),1)>=6,
ROUNDUP(ROUNDDOWN(G15*(E15-F15),3),2),
ROUNDDOWN(ROUNDDOWN(G15*(E15-F15),3),2))
+
IF(--RIGHT(ROUNDDOWN(H15*(I15-E15),3),1)>=6,
ROUNDUP(ROUNDDOWN(H15*(I15-E15),3),2),
ROUNDDOWN(ROUNDDOWN(H15*(E15-E15),3),2))

[Engineer445]

sorry forgot to attach the example spreadsheet:

[AliGW]

Several years later ... so please start a new thread. Thanks.