Formula Help

[pjd]

Hi

I hope this is possible:

In cell M8 I have =COUNT(C8:I8)
In cell K8 I have =IF(M8*7.6<39,M8*8,40)
In cells C8:I8 there can be numbers eg 730, 789 but also there can text eg AL or RDO.
What I need to calculate is; if the result in M8 =6 or more and if any cells in that range have text eg RDO, AL, SL, PH, or TN, then subtract 8 for each instance from M8.

If anyone could help I'd appreciate. Also does anyone know of any books on excel that are dedicated to formulas and their uses but also gives great examples of their uses.

 

[Biff]

Hi pjd!

Here's one way:

=IF(M8>=6,M8-ABS(COUNTA(C8:I8)-COUNT(C8:I8))*8,M8)

You didn't specify what to do if those conditions are not
met (FALSE) so the formula just returns M8.

Books:

http://contextures.com/xlbooks.html

Biff

-----Original Message-----
Hi

I hope this is possible:

In cell M8 I have =COUNT(C8:I8)
In cell K8 I have =IF(M8*7.6<39,M8*8,40)
In cells C8:I8 there can be numbers eg 730, 789 but also there can text eg AL or RDO.
What I need to calculate is; if the result in M8 =6 or

more and if any cells in that range have text eg RDO, AL,
SL, PH, or TN, then subtract 8 for each instance from M8.

If anyone could help I'd appreciate. Also does anyone

know of any books on excel that are dedicated to formulas
and their uses but also gives great examples of their uses.

 

[pjd]

Hi
thanks for the reply

sorry I wasn't clear enough. I need to put the formula in k8, when I put your formula in i get 4 instead of 40 - the number of text cells (each text cell =8)

For instance

in cells C8:I8 I have 730, 730, 730, rdo, rdo, 730, the result with your formula is 4, the cells need to return 40 or 40-the number if text incidents eg in this case the result should be 24. I have to have a result that is either 40 or 40 less the numbe r of text incidents (each incident =8).

I hope this makes better sense, I really appreciate your help.

thanks
--------------

 

[Biff]

Hi pjd!

Now I'm confused!!!! You're changing conditions!!!

....range have text eg RDO, AL, SL, PH, or TN, then subtract 8
for each instance from M8.

With M8 formula: Count(C8:I8)the result is 4 which is less
than 6 so the formula returns M8. If you want it to return
40 when the conditions are false just change the formula
to:

=IF(M8>=6,M8-ABS(COUNTA(C8:I8)-COUNT(C8:I8))*8,40)
result with your formula is 4, the cells need to return
40 or 40-the number if text incidents eg in this case the
result should be 24.

No, because Count(C8:I8)is less than 6. Unless you don't
want the >=6 condition to apply, but that's what you asked
for!!!!

If you want it to either return 40 or 40 minus 8*TEXT, try
this:

=IF(ABS(COUNTA(C8:I8)-COUNT(C8:I8))>0,40-ABS(COUNTA(C8:I8)-
COUNT(C8:I8))*8,40)

Biff

-----Original Message-----
Hi
thanks for the reply

sorry I wasn't clear enough. I need to put the formula in

k8, when I put your formula in i get 4 instead of 40 - the
number of text cells (each text cell =8)

For instance

in cells C8:I8 I have 730, 730, 730, rdo, rdo, 730, the

result with your formula is 4, the cells need to return
40 or 40-the number if text incidents eg in this case the
result should be 24. I have to have a result that is
either 40 or 40 less the numbe r of text incidents (each
incident =8).