I want to write a formula that changes when the previous column reaches a certain amount. I assume it can be done using SUMIF but thats where my knowledge ends.
Example. B2 is SUM(A2*.45) , B3 is SUM(A3*.45) and B4 is SUM(A4*.25)
What i need to do is create a formula that changes the *.45 to *.25 when the previous column reaches 10000.
Thanks for any help.
I cannot find a good reason to use the SUM function in the case of your example.
(B2:) =A2*IF(A2>=10000;0.25;0.45)
and respectively.
Editing 2016-05-11 21:10UTC with respect to the comment below.
It is really difficult to understand your intentions. From the added examples I would guess you want to split any amount into its lower part of up to 10000 weighted by a factor of 0.45, and the upper part exceeding 10000 and weighted by the factor 0.25. If so use =MIN(A2,10000)*0.45+MAX(0,A2-10000)*0.25 or =A2*0.25+MIN(A2,10000)*0.2
I still cannot decide in what way the formula should treat negative values.
Thanks LUPP,
im having one small issue with the formula.
If the figure in B2 is 9999 the formula works fine (gives answer of 4499.55). If the figure in B2 is 10001 the answer becomes 2500.25, where it should be 4500.25.
What i need the formula to do is caulculate the *.45 up to 10000 then if the number is over 10000 calculate the first 10000 at .45 the anything above that at.25, so as per my example 10001 should give 4500.25, 10002 would give 4500.5 etc.
any idea how i achieve that?
Thanks again Lupp. The second one did what i wanted.