Function help please!

[R.P.McMurphy]

HI all, I have six cells; c9,c17,c25,j9,j17,j25

if the number entered in any of these cells is over 39 then I want to total
the number over that amount. is that clear? for example-

cell c9 has 30
cell c17 has 39
cell c25 has 40
cell j9 has 23
cell j17 has 42
cell j25 has 2

the answer would be 3

Any help gratefully received! (again)

Steve

 

[Barb Reinhardt]

First, how are you determining the # is three. I see one cell with a value
greater than 39.

 

[Barb Reinhardt]

Correction ... it's still early here ... when you say over 39 do you really
mean >= 39?

 

[R.P.McMurphy]

i.e. 39=0 whereas 40=1 i.e. is 1 over 39.

cheers!

Steve

 

[Barb Reinhardt]

I can get it to partially work with this

=COUNTIF(C9:I25,">=39")

But am unable to put a range in cell by cell. Maybe someone else can assist
here.

 

[Ken Johnson]

Hi Steve,
39=0 OK
40=1 OK
42=3 OK?
Now, 1 + 3 = 4 OK?
How come you get 3?

My (yukky) formula is:

=(SUM(C9,C17,C25,J9,J17,J25) - 6*39 + ABS(C9-39) + ABS(C17-39) +
ABS(C25-39)+ ABS(J9-39) + ABS(J17-39) + ABS(J25-39))/2

which gives the result 4 with the values supplied.

Ken Johnson

 

[R.P.McMurphy]

hehe...you spotted the deliberate mistake!

many thanks all!

steve

 

[Bob Phillips]

How about

=SUMPRODUCT(--(N(OFFSET(C9:C25,{0,8,16},0,1,1))>=39))+SUMPRODUCT(--(N(OFFSET
(J9:J25,{0,8,16},0,1,1))>=39))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[R.P.McMurphy]

I have changed it to

=SUMPRODUCT(--(N(OFFSET(table1 C9:table1
C25,{0,8,16},0,1,1))>=39))+SUMPRODUCT(--(N(OFFSET(table1 J9:table1
J25,{0,8,16},0,1,1))>=39))

but its still not working,

help!

Steve

 

[R.P.McMurphy]

I put 'table1' in fornt of all the c9,c17,c25 j9 s... ect, but all I am
getting is 9, no mater what I enter into the fields.

Steve

 

[Bob Phillips]

Try

=SUMPRODUCT(--(N(OFFSET('table1'!C9:C25,{0,8,16},0,1,1))>=39))+SUMPRODUCT(--
(N(OFFSET('table1'!J9:J25,{0,8,16},0,1,1))>=39))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[R.P.McMurphy]

no its still not working. I am not even getting a result.

Steve

 

[R.P.McMurphy]

working with this one Bob, I just open a new spread sheet up and entered the
figures and your original formula. it does indeed work for the figures
quoted. but if I change c25 to 41 the result doesn't change, it should go
up by one i.e. 4. like wise, if I changed j17 to 40 the result should be 1.

Steve

 

[R.P.McMurphy]

Ken, yours is working well. my problem is I am using this formula in MS
Word in a table to be used as a form. the answer goes in table 2 and the
reference cells go in table 1. how would one change it to refer to the
cells in table1? the formula as you have written it works great if used in
the same table as the reference cells.

cheers

Steve

 

[Bob Phillips]

It changes for me.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ken Johnson]

Hi Steve,
Sorry Steve, I had to get to bed, it was the wee hours of the morning
in Sydney.
I don't know much about Word and what you're trying to do.
Maybe the fact that you're in Word explains why Bob's SUMPRODUCT
formula didn't work.
Word's math ability is much much less than Excel's and it was just
fortuitous that my formula only contains SUM and ABS, which Word
probably has.
All I can suggest is that you post your problem in one of the Word
groups, such as microsoft.public.word.docmanagement.
Sorry I can't do more than that, but I will be thinking about this
problem of linking Word table values and will let you know if I have
any success.
Ken Johnson

 

[R.P.McMurphy]

Thanks for that Ken, will try over there!

Steve