Home

Request for Experts Formulas for this Calculation

%3CLINGO-SUB%20id%3D%22lingo-sub-758165%22%20slang%3D%22en-US%22%3ERequest%20for%20Experts%20Formulas%20for%20this%20Calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-758165%22%20slang%3D%22en-US%22%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3ECurrency%20SGD%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3CTD%3ELess%2015%25%20of%20Total%3C%2FTD%3E%3CTD%3ETotal%20with%203%25%20GST%3C%2FTD%3E%3CTD%3ETotal%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESingapore%20Dollars%3C%2FTD%3E%3CTD%3E9.00%3C%2FTD%3E%3CTD%3E10.30%3C%2FTD%3E%3CTD%3E10.05%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESingapore%20Dollars%3C%2FTD%3E%3CTD%3E4.30%3C%2FTD%3E%3CTD%3E5.20%3C%2FTD%3E%3CTD%3E5.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESingapore%20Dollars%3C%2FTD%3E%3CTD%3E4.30%3C%2FTD%3E%3CTD%3E5.20%3C%2FTD%3E%3CTD%3E5.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESingapore%20Dollars%3C%2FTD%3E%3CTD%3E5.10%3C%2FTD%3E%3CTD%3E6.20%3C%2FTD%3E%3CTD%3E6.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESingapore%20Dollars%3C%2FTD%3E%3CTD%3E8.40%3C%2FTD%3E%3CTD%3E10.20%3C%2FTD%3E%3CTD%3E9.90%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESingapore%20Dollars%3C%2FTD%3E%3CTD%3E4.20%3C%2FTD%3E%3CTD%3E5.00%3C%2FTD%3E%3CTD%3E4.90%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESingapore%20Dollars%3C%2FTD%3E%3CTD%3E10.20%3C%2FTD%3E%3CTD%3E12.40%3C%2FTD%3E%3CTD%3E12.00%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20say%20If%20I%20only%20have%20data%20on%20the%20Total%20amount%2C%20how%20do%20I%20go%20about%20entering%20the%20formula%20for%20%3CSPAN%3ELess%2015%25%20of%20Total%20and%26nbsp%3BTotal%20with%203%25%20GST%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EKindly%20help%20me%2C%20thks!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-758165%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-758253%22%20slang%3D%22en-US%22%3ERe%3A%20Request%20for%20Experts%20Formulas%20for%20this%20Calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-758253%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F377375%22%20target%3D%22_blank%22%3E%40Superhero3984%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20tried%20to%20build%20a%20formula%20that%20went%20from%20your%20Total%20column%20to%20each%20of%20the%20other%20two%2C%20but%20the%20results%20were%20inconsistent.%20The%20closest%20I%20got%20was%20to%20assume%20amounts%20should%20be%20rounded%20to%20the%20nearest%205%20cents.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20following%20formula%20assumes%20your%20sample%20data%20are%20in%20A2%3AD2.%20Values%20in%20D2%20are%20given.%20The%20formulas%20for%20B2%20and%20C2%20(less%2015%25%20discount%20and%20plus%203%25%20GST)%20are%20shown%20below%3A%3C%2FP%3E%0A%3CPRE%3E%3DMROUND(D2*0.85%2C0.05)%0A%3DMROUND(D2*1.03%2C0.05)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-766738%22%20slang%3D%22en-US%22%3ERe%3A%20Request%20for%20Experts%20Formulas%20for%20this%20Calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-766738%22%20slang%3D%22en-US%22%3EThank%20you%20Sir%2C%3CBR%20%2F%3EIt%20do%20help%20in%20some%20way.%20However%2C%20I%20required%20to%20derive%20to%20a%20nearest%200.10%20cent%20if%20its%20above%200.5%20should%20be%201.0%20then.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-766746%22%20slang%3D%22en-US%22%3ERe%3A%20Request%20for%20Experts%20Formulas%20for%20this%20Calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-766746%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F377375%22%20target%3D%22_blank%22%3E%40Superhero3984%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20figures%20in%20your%20example%20table%20neither%20round%20up%20to%20the%20dollar%20nor%20to%2010%20cents.%20So%20I%20don't%20understand%20how%20to%20interpret%20%22I%20required%20to%20derive%20to%20a%20nearest%200.10%20cent%20if%20its%20above%200.5%20should%20be%201.0%20then.%22%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEither%20revise%20your%20example%20table%2C%20or%20clarify%20how%20you%20want%20the%20calculations%20to%20be%20done.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Superhero3984
New Contributor
Currency SGD                 Less 15% of TotalTotal with 3% GSTTotal
Singapore Dollars9.0010.3010.05
Singapore Dollars4.305.205.00
Singapore Dollars4.305.205.00
Singapore Dollars5.106.206.00
Singapore Dollars8.4010.209.90
Singapore Dollars4.205.004.90
Singapore Dollars10.2012.4012.00

 

Let say If I only have data on the Total amount, how do I go about entering the formula for Less 15% of Total and Total with 3% GST?

 

Kindly help me, thks!

3 Replies

@Superhero3984 

I tried to build a formula that went from your Total column to each of the other two, but the results were inconsistent. The closest I got was to assume amounts should be rounded to the nearest 5 cents.

 

The following formula assumes your sample data are in A2:D2. Values in D2 are given. The formulas for B2 and C2 (less 15% discount and plus 3% GST) are shown below:

=MROUND(D2*0.85,0.05)
=MROUND(D2*1.03,0.05)
Thank you Sir,
It do help in some way. However, I required to derive to a nearest 0.10 cent if its above 0.5 should be 1.0 then.

@Superhero3984 

The figures in your example table neither round up to the dollar nor to 10 cents. So I don't understand how to interpret "I required to derive to a nearest 0.10 cent if its above 0.5 should be 1.0 then."

 

Either revise your example table, or clarify how you want the calculations to be done.

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies