 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / December 2005Function help please!
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 First, how are you determining the # is three. I see one cell with a value greater than 39. > HI all, I have six cells; c9,c17,c25,j9,j17,j25 > [quoted text clipped - 13 lines] > > Steve Correction ... it's still early here ... when you say over 39 do you really mean >= 39? > HI all, I have six cells; c9,c17,c25,j9,j17,j25 > [quoted text clipped - 13 lines] > > Steve i.e. 39=0 whereas 40=1 i.e. is 1 over 39. cheers! Steve > Correction ... it's still early here ... when you say over 39 do you > really mean >= 39? [quoted text clipped - 16 lines] >> >> Steve 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. > HI all, I have six cells; c9,c17,c25,j9,j17,j25 > [quoted text clipped - 13 lines] > > Steve 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 hehe...you spotted the deliberate mistake! many thanks all! steve > Hi Steve, > 39=0 OK [quoted text clipped - 11 lines] > > Ken Johnson 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))  SignatureHTH Bob Phillips (remove nothere from email address if mailing direct) > Hi Steve, > 39=0 OK [quoted text clipped - 11 lines] > > Ken Johnson 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 > How about > [quoted text clipped - 16 lines] >> >> Ken Johnson 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)) SignatureHTH Bob Phillips (remove nothere from email address if mailing direct) > I have changed it to > [quoted text clipped - 8 lines] > Steve > > 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)) > > [quoted text clipped - 13 lines] > >> > >> Ken Johnson no its still not working. I am not even getting a result. Steve > Try > [quoted text clipped - 32 lines] >> >> >> >> Ken Johnson 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 > How about > [quoted text clipped - 16 lines] >> >> Ken Johnson It changes for me. SignatureHTH RP (remove nothere from the email address if mailing direct) > 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 text clipped - 4 lines] > > > 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)) > > [quoted text clipped - 13 lines] > >> > >> Ken Johnson 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 > Hi Steve, > 39=0 OK [quoted text clipped - 11 lines] > > Ken Johnson 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 > Hi Steve, > 39=0 OK [quoted text clipped - 11 lines] > > 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 Thanks for that Ken, will try over there! Steve > Hi Steve, > Sorry Steve, I had to get to bed, it was the wee hours of the morning [quoted text clipped - 11 lines] > any success. > Ken Johnson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.