Formula Question

[Guest]

We only charge for a meal tray served if the number is over three per month;
if they only get three, no charge, if they get four, we charge for one, five,
we charge for two and so on.

Is there a formula that will provide the following:

If the sum of the preceeding three cells (totalling the number of breakfast,
lunch and dinner trays) is = or < 3, I need it to return 0; however, if the
number is greater than 3, I need it to return the actual number minus 3.

Any help?

 

[Don Guillett]

Look in the help index for COUNTIF

 

[Guest]

Thanks Don, but that isn't what I need. That only counts the number of cells
that meet the criteria, I need a formula to produce the values.

Wanda

 

[RagDyer]

You mention the "preceeding 3 cells".

Say A1, B1, and C1 contain the numbers in question.
In D1, try this:

=MAX(SUM(A1:C1)-3,0)

 

[Don Guillett]

Still not quite sure what you want. Try this which SUMS.
=IF(SUM(B66)>3,SUM(B66)-3,"")

 

[Guest]

Perfect! Thank you!

You mention the "preceeding 3 cells".

Say A1, B1, and C1 contain the numbers in question.
In D1, try this:

=MAX(SUM(A1:C1)-3,0)

 

[RagDyeR]

Appreciate the feed-back.