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
Mdcryan
Frequent Visitor

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

@Mdcryan 

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.

@Mdcryan , 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.