formulas...still trying

  • Thread starter Thread starter vince
  • Start date Start date
V

vince

=((E8>{0,250,499.99})*{0.25,0.1,0.15})

I tried Jason's first here is the way I set it up. It
never reports anything other thank .00 or .25.

So, it is missing the 35% and the 50%.

Here was the original question.
"How would I do a formula as follows: thanks in advance.
0-$250.00 pays 25%
$250.01 - 499.99 pays 35%
500.00 + pays 50%"
 
It is helpful if you stick to the same thread, this will work

=SUMPRODUCT(--(E8>{0;250;500}),(E8-{0;250;500}),{0.25;0.1;0.15})

this assumes that if the value in E8 is 600, then the returns are
62.50 + 87.50 +50
0-250, 250-500,over500

total is 200
 
Vince,

It is missing a crucial part

=SUM(((E8>{0,250,499.99})*{0.25,0.1,0.15}))

If you want the %ge amount, then use

=E8*SUM(((E8>{0,250,499.99})*{0.25,0.1,0.15}))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks for the tip.

I am almost there. 0-250.00 appears to be working $250.01
to $499.99 is not coming out correctly, neither is the
$500.00 plus.

87.96 came out as $21.99 (perfect)
287.96 came out as $75.78, Should have been 35% ($100.78)
687.96 came out as $243.98, Should have been 50% ($121.99)
 
=INDEX({0.25,0.35,0.5},MATCH(E8,{0,250,500})+(VLOOKUP(E8,{0,250,500},1)=E8))

where houses a value of interest. Or, for completeness...

=IF(E8,INDEX({0.25,0.35,0.5},MATCH(E8,{0,250,500})+(VLOOKUP(E8,{0,250,500},1
)=E8)),0)
 
Hi Vince

Bob's formula works correctly for me, 50%, 25% and 25% respectively
687.96 343.98
287.96 100.786
87.96 21.99
 
Vince,

I think Peo read it as the first 250 is at 25%, then the next 250 at 35%,
then the rest at 35%.

I assume the $121,99 for 50% should have been $343.98.

Try my previous response

Vince,

It is missing a crucial part

=SUM(((E8>{0,250,499.99})*{0.25,0.1,0.15}))

If you want the %ge amount, then use

=E8*SUM(((E8>{0,250,499.99})*{0.25,0.1,0.15}))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
The %ages came out perfect, now how hard is it to make it
a dollar amount.

I should have mentioned it earlier.

Thanks for all the great help.
 
Correction:

=INDEX({0.25,0.35,0.5},MATCH(A2,{0,250,500})-(VLOOKUP(A2,{0;250;500},1)=A2))
 
Are you saying that if you have over 250 the amount should 35% of the whole
value, that's a bit weird isn't it?

=SUMPRODUCT(E8*LOOKUP(E8,{0;250;500},{0.25;0.35;0.5}))
 
Yes, is is set up to give more %age if you get a higher
return.

Will this return a dollar amount?

Thanks
 
Dollar amount, just format as currency. I didn't doubt that it was setup to
give a higher %
if you get a higher return, most commissions probably use what I first
posted, assume 600, then for the first
250 you get 25%, for the next 250 you get 35% and for the last 100 you get
50%, but you are obviously using
50% for the whole amount.
 
=SUMPRODUCT(((E17>{0,250,499.99})*{0.25,0.1,0.15}))

This one seems to be working correctly.

But it is giving me a .25/.35/.50 of the total individual
amount that is in block E17, what I need is the exact
dollar amount....like $100.00 / .25 = $25.00
$255.00 / .35 = $89.25
$501.00 / .50 = $250.50
 
You have been given 3 formulas that can give you the exact dollar amount:

[1]

=SUMPRODUCT(E17*LOOKUP(E17,{0;250;500},{0.25;0.35;0.5}))

[2]

=E17*SUM(((E17>{0,250,499.99})*{0.25,0.1,0.15}))

[3]

=E17*INDEX({0.25,0.35,0.5},MATCH(E17,{0,250,500})-(VLOOKUP(E17,{0;250;500},1
)=E17))

The formula in [2] must be confirmed with control+shift+enter instead of
just enter.

All behave the same way, except [3] with an exact amount of 500.

So, What is the trouble?
 
Vince,

the second part of my post gives the dollar amount

=E8*SUM(((E8>{0,250,499.99})*{0.25,0.1,0.15}))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top