Reference range of cells and perform subtraction or nothing depending on contents
 

Thanks in advance for the help. My sheet is for calculating per-diem for employees. Their starting per-diem is 600, and every day they work, I subtract 30 from the 600. So, on days that they work, I put a "W" in the corresponding day and an "O" if they don't work. Is there a formula that will examine a range of cells containing "W" or "O" and subtract 30 if "W" appears and do nothing if "O" appears, then keep a running total of the per-diem that is left?

Truly,

Robert

Reference range of cells and perform subtraction or nothing depending on contents
 

=MAX(0,600-COUNTIF(A:A,"W")*30)

where you put the W and O in column A

--
Regards,

Peo Sjoblom

Portland, Oregon




"bob-hamilton" wrote in message
...

Thanks in advance for the help. My sheet is for calculating per-diem
for employees. Their starting per-diem is 600, and every day they
work, I subtract 30 from the 600. So, on days that they work, I put a
"W" in the corresponding day and an "O" if they don't work. Is there a
formula that will examine a range of cells containing "W" or "O" and
subtract 30 if "W" appears and do nothing if "O" appears, then keep a
running total of the per-diem that is left?

Truly,

Robert


--
bob-hamilton

Thanks for the post,

I have an answer that worked good,

=600-30*COUNTIF(range,"W")

Where range is the range of cells that contain "W" or "O"

Bye for now.

Robert

Reference range of cells and perform subtraction or nothing depending on contents
 

It does the same thing, the only difference is that my stop at 0 while the
one you have goes to negative if the number of Ws will be more than 20,
don't know why but I assumed you wanted to subtract until you got to 0

--
Regards,

Peo Sjoblom

Portland, Oregon




"bob-hamilton" wrote in message
...

Thanks for the post,

I have an answer that worked good,

=600-30*COUNTIF(range,"W")

Where range is the range of cells that contain "W" or "O"

Bye for now.

Robert



Peo Sjoblom Wrote:
=MAX(0,600-COUNTIF(A:A,"W")*30)

where you put the W and O in column A

--
Regards,

Peo Sjoblom

Portland, Oregon




"bob-hamilton" wrote in
message
...

Thanks in advance for the help. My sheet is for calculating per-diem
for employees. Their starting per-diem is 600, and every day they
work, I subtract 30 from the 600. So, on days that they work, I put
a
"W" in the corresponding day and an "O" if they don't work. Is there
a
formula that will examine a range of cells containing "W" or "O" and
subtract 30 if "W" appears and do nothing if "O" appears, then keep a
running total of the per-diem that is left?

Truly,

Robert


--
bob-hamilton


--
bob-hamilton