IF formula

S

SSPAIN

i am attempting to write a formula that says
if c$284<33000000 then c260*.004 or if c>33000000 then c260*.0028 Please help!
 
B

Bryan Hessey

=If(C$284<33000000,C$260*.004,If(C$284>33000000,C$260*.0028,""))

but you probably didn't want to leave =33000000 - so

=If(C$284<33000000,C$260*.004,C$260*.0028,"")
or
=If(C$284<=33000000,C$260*.004,C2$60*.0028,"")

Hope this helps

--
 
S

SSPAIN

Thanks I have tried that one but for some reason it changes the values of the
preceeding cells. This is a rate change formula I need to show daily
according to the amount collected. I am at a lost .
 
A

Alan

Can you give a little more detail? what do you mean by 'but for some reason
it changes the values of the preceeding cells'?
Alan.
 
S

SSPAIN

Yes i am posting daily payments. The rate before we reach mtd total of 33M
is the total collected for the day x.004, once the MTD( month to date) total
reaches 33M the daily rate changes to .0028. I need the MTD to reflect the
total of both rates once the rate changes.
Can you give a little more detail? what do you mean by 'but for some reason
it changes the values of the preceeding cells'?
Alan.
Thanks I have tried that one but for some reason it changes the values of
the
Click to expand...
[quoted text clipped - 5 lines]
Click to expand...
 
B

Bryan Hessey

so you want to say:

=If(C$284<33000000,C$260*.004,(33000000*.004)+(C$260-33000000)*.0028)

ie, the first 33m at .004, the rest at .0028 ?

--
 
S

SSPAIN via OfficeKB.com

Thanks so much for your help. I think I am missing a step. i want each day to
calculate at .004 until the mtd is $33,000,000 then once the mtd reaches that
point the daily collections rate is .0028

Bryan said:
so you want to say:

=If(C$284<33000000,C$260*.004,(33000000*.004)+(C$260-33000000)*.0028)

ie, the first 33m at .004, the rest at .0028 ?

--
Yes i am posting daily payments. The rate before we reach mtd total of
33M
Click to expand...
[quoted text clipped - 22 lines]
Click to expand...
 
B

Bryan Hessey

I presume that your daily entries are on consecutive rows, if so the
formula drag the calculation down each row, but you will need to use
point other than C$284 for your test and other than C$260 for th
amount to be used, othewise when C284 passes 33m then all rows wil
change calculation.
What formula is in C284 and C260 ?

Perhaps something like:


=if(C2="","",If(SubTotal(9,C$2:C2)<=33000000,SubTotal(9,C$2:C2)*.004,(33000000*.004)+(SubTotal(9,C$2:C2)-33000000)*.0028))

would be more appropriate.


If you are amending the same cell each day then you will need some V
Code to detect your change and accumulate the total to date.

Hope this helps

--
 
Top