Home

SUMPRODUCT formula persistent fail. UK Stamp Duty Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-531347%22%20slang%3D%22en-US%22%3ESUMPRODUCT%20formula%20persistent%20fail.%20UK%20Stamp%20Duty%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-531347%22%20slang%3D%22en-US%22%3E%3CP%3EFound%20the%20following%20well%20commented%20and%20working%20formula%20for%20calculating%20U.K.%20stamp%20duty%3C%2FP%3E%3CP%3E%3DSUMPRODUCT(--(%24B%243%26gt%3B%7B125000%2C250000%2C925000%2C1500000%7D)%2C%20(%24B%243-%7B125000%2C250000%2C925000%2C1500000%7D)%2C%20%7B0.02%2C0.03%2C0.05%2C0.02%7D)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehowever%20when%20I%20paste%20this%20into%20excel%20i%20get%20the%20following%20warning.%20Tried%20debugging%2C%20syntax%20everything%20but%20can%E2%80%99t%20see%20what%20I%20am%20doing%20wrong.%20Any%20help%20gratefully%20received!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F112123iFC2D0002C9071CF6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%225D94BE42-8D2C-4834-8CF7-F36DAF1D5795.png%22%20title%3D%225D94BE42-8D2C-4834-8CF7-F36DAF1D5795.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-531347%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-532034%22%20slang%3D%22en-US%22%3ERe%3A%20SUMPRODUCT%20formula%20persistent%20fail.%20UK%20Stamp%20Duty%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-532034%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F333669%22%20target%3D%22_blank%22%3E%40Mdcryan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20SUMPRODUCT%20evaluates%20as%20a%20valid%20formula%20on%20my%20machine.%26nbsp%3B%20Are%20there%20any%20spurious%20characters%20(maybe%20non-printing)%20in%20your%20formula%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20try%20editing%20the%20formula%20to%20read%3C%2FP%3E%3CP%3E%3DSUMPRODUCT((amount%26gt%3B%7B125%2C250%2C925%2C1500%7D)*(amount-%7B125%2C250%2C925%2C1500%7D)%2C%7B0.02%2C0.03%2C0.05%2C0.02%7D)%3C%2FP%3E%3CP%3EThe%20product%20would%20remove%20the%20need%20for%20the%20double%20negative%20coercion%20from%20Boolean%20to%20numeric%20and%20at%20the%20same%20time%20replace%20the%20characters%20reported%20at%20the%20error%20point.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-532157%22%20slang%3D%22en-US%22%3ERe%3A%20SUMPRODUCT%20formula%20persistent%20fail.%20UK%20Stamp%20Duty%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-532157%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F333669%22%20target%3D%22_blank%22%3E%40Mdcryan%3C%2FA%3E%26nbsp%3B%2C%20try%20to%20check%20what%20are%20your%20default%20list%20separator%20(comma%2C%20semicolon)%20and%20decimal%20separator%20(dot%2C%20comma)%20and%20if%20you%20correctly%20use%20them%20in%20formula.%20I%20guess%20you%20are%20on%20iPad%2C%20don't%20know%20where%20such%20basic%20settings%20are%20for%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Deleted
Not applicable

Found the following well commented and working formula for calculating U.K. stamp duty

=SUMPRODUCT(--($B$3>{125000,250000,925000,1500000}), ($B$3-{125000,250000,925000,1500000}), {0.02,0.03,0.05,0.02})

 

however when I paste this into excel i get the following warning. Tried debugging, syntax everything but can’t see what I am doing wrong. Any help gratefully received!

 

5D94BE42-8D2C-4834-8CF7-F36DAF1D5795.png

2 Replies

@Deleted 

The SUMPRODUCT evaluates as a valid formula on my machine.  Are there any spurious characters (maybe non-printing) in your formula?

 

You could try editing the formula to read

=SUMPRODUCT((amount>{125,250,925,1500})*(amount-{125,250,925,1500}),{0.02,0.03,0.05,0.02})

The product would remove the need for the double negative coercion from Boolean to numeric and at the same time replace the characters reported at the error point.

@Deleted , try to check what are your default list separator (comma, semicolon) and decimal separator (dot, comma) and if you correctly use them in formula. I guess you are on iPad, don't know where such basic settings are for it.

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