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
Add Import/Export Flags option
HotCakeX in Discussions on
7 Replies
Duplicate Extensions and Abusing Extension report
HotCakeX in Discussions on
6 Replies
SUM formula help needed with multiple criteria
Eileen Skidgel in Excel on
5 Replies
Help with #NUM error in IRR formula
hasan ahmed in Excel on
2 Replies