Hi

I am trying to tie one cell into a formula that is being pasted into multiple rows.

I know you can use $ to keep the cell consistent but it doesn't seem to be working.

So if the value is in c9 and then first row gets c9 then the new row gets c10 etc... I need it to be c9 throughout the column.

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link __ http://downloadreimage.com/download.php__. (This link will automatically start a download of Reimage that you can save to your computer.)

You use $C9 to keep the column consistent, C$9 to keep the row consistent and $C$9 to keep the cell consistent. Are you doing that and it's not working?

Read other 2 answersFor some reason I cannot get Excel 2000 to display the results of a formula. Only the formula text displays. I've gone to Tools-Options-View-Windows Options and checked and unchecked the Formulas checkbox. It doesn't make any difference.

Help!

Thanks,

Thomas

Format the cell as a number, it prolly is fmt'd as text

Hi ~

I'm working on a financial formula, trying to calculate a growth rate for a row of cells. The problem is that not all rows have complete data sets, so the formula picks up blank cells and returns a #DIV/0! error.

The existing formula works fine for those rows that have non-zero entries in all cells, and I *can* manually adjust the formula for the starting / ending cells in each row--but what a pain(!). As this spreadsheet grows larger, or the data sets change over time, it means I have to constantly keep adjusting it for every row of data involved, which is extremely time-consuming.

Here's an short example of what I might have in any given row:

A1 (blank), B1(blank), C1(blank), D1(.10), E1(.25), F1(.72), G1(1.0), H1(.80), I1(.60), J1(.75)--then in K1 is the growth formula, e.g. =((J1/A1)^(1/10)-1).

Is there ANY way to create a formula that will dynamically adjust for the blank cells that might exist across a row of data and let the formula do the calculation?

I'm new to advanced Excel topics, but any ideas would be MOST appreciated(!).

Thanks -

CP

Hi, I need to do the following:

Search array

1 a 12 asdas

2 b 121 agrsfsa

3 c 3 qwgds

4 b 131 erwer

5 e 4123 q3eqt

need to return something like this

lookup value b

answer should look like this

2 b 121 agrsfsa

4 b 131 erwer

index, match can give me one row, however what could return multiple values

Thanks in advance for your help.

Cheers

Read other 8 answers

If it's possible to do this, what is the formula syntax for an if/then formula in one sheet that refers to the cell value in another sheet in the same workbook?

For example in Sheet 2, the formula would be something like, if Cell A10 in Sheet 1 is greater than 0, then the value of Cell B20 in Sheet 2 would by "Yes" but it would be "No" if the valuein Cell A10 in Sheet 1 was less than 0.

I have created a formula in Excel that works fine, but when I put it into a MS Word table, it doesn't seem to work. The formula adds up a column of prices when the resulting column has a specific word in.

The formula is:

=SUMIF(E:E, "Yes", D:D)

Example:

Is this possible to do? If so can anyone help converting this so it will work in MS Word.

Thanks, Adam.

You'll have to do this:

https://support.office.com/en-us/art...4-0d7e88062257

Hi,

I am trying to pull a dashboard view from a "Tracker". Given below is the formula that I used:

=COUNTIFS('KT Tracker'!$T$2:$T$138,$C$2,'Tracker'!$U$2:$U$138,"<="&TODAY()+7,'Tracker'!$L$2:$L$138,'Monthly Dashboard'!$B17,'Tracker'!$AF$2:$AF$138,'Monthly Dashboard'!$A$13,'Tracker'!$AD$2:$AD$138,"<>Cancelled")

This perfectly works fine in my laptop that has excel 2013 but when I email this to my team who have Excel 2010, they only see zeros. We have checked all options like enabling Automatic Calulcation of Formula, removing all named ranges in the sheet etc., but it is just not working.

Can you please help ? This is a bit urgent and I need to present this for a meeting tomorrow.

Appreciate your help and thanks in advance !!!

It might be something to do with "<>cancelled"

I cannot search for data in excel 2013. This is new issue. I do notice that in the find window there is an entry for Preview* in the box next to format in the options window that I have never seen before. I get the message cannot find what you are looking for but I know it is there because I am looking at it.

This is new issue. Worked until last night when I was doing a sort and had to make all merged cells uniform in size. Now I can sort but cannot find.

Have closed and reopened excel, but it did not correct problem.

I am not very proficient with Excel and need formula help. I am putting together a spreadsheet for a party. It shows names of people, # in party (i.e.2 if it is a couple) and whether they are attending (Y or N). Is it possible to write a formula that shows that if they are not attending, then the # in party is 0? Would also apply to their kids attending. Thanks in advance

I am looking to create a spreadsheet that calculates vacation accrual and takes into account Flex Days, etc. Vacation accrual is accumulated at 6% of someones gross pay so at the end of 52 weeks, it would add up to their alotted vacation. I.E. I get 3 weeks a year. Not everyone gets Flex Days so I would only calculate this for those that get them.

Are you able to assist me in coming up with the proper formula?

i have a time sheet im putting together and i need help:

If cell A8 = a number other then 99 put the Letter "M" in cell E8

but if cell A8 = 99 put 99 in E8

but if cell A8 is blank put "A" in E8

thank in advance

The formula you need in M8 is

=IF(E8=99,99,IF(A8="","A","M"))Click to expand...

Then copy that down.

Also if you custom format the date as

dddd d/m/yyyy Click to expand...

Excel will include the day for you automatically.

OK - I need a formula to calculate the following

In Cell A1 I will enter a number anywhere from 0-1000. In column B (I have a list from 1-1000) and in column C (I have what each numbe from column B will return with a LOOKUP formula - lets say they are people's names)

If cell A1 is equal to 6, 12, 18, 24, 30, 36, 42, 48, (you see that the pattern here is increments of 6) then I want it to ADD 1 to cell A2 if not then I want it to look up the number listed in column B and give me the corresponding name in column C.

Is there anyone who can help me - it's driving me crazy!

Thank you sooo much!

t long said:

OK - I need a formula to calculate the following

In Cell A1 I will enter a number anywhere from 0-1000. In column B (I have a list from 1-1000) and in column C (I have what each numbe from column B will return with a LOOKUP formula - lets say they are people's names)

If cell A1 is equal to 6, 12, 18, 24, 30, 36, 42, 48, (you see that the pattern here is increments of 6) then I want it to ADD 1 to cell A2 if not then I want it to look up the number listed in column B and give me the corresponding name in column C.

Is there anyone who can help me - it's driving me crazy!

Thank you sooo much!Click to expand...

Try,

=IF(MOD(A1,6)=0,A2+1,VLOOKUP(A1,B1:C10,2,0))

Is there a way I can make a formula in Excel so that it will only count certain combo's of numbers that up to certain numbers?

for instance... im working on a project for a statistics class and I have to make a game with payouts and such.

I have generated two columns of random numbers 1-6

It looks like this: (the numbers represent colors, 1=red, 2=orange etc)

Color 1 Color 2 Total Win/Lose

2 6 8 Win

4 4 8 Win

5 2 7 Lose

6 3 9 Lose

I have the formula for the Win/Lose column set up so that if the number in the total column is 4,6, or 8 it will say "win" but, i only wanted to count 1+3, and 3+1 for the number 4, but as we all know 2+2=4 aswell and I cant have that.

So I need help making my excel sheet only recognizing the follwing as a "Win":

1+3 =4

3+1 =4

1+5 =6

5+1 =6

3+5 =8

5+3 =8

I attached a sample of my work.

My question is how do I input a formula into Office 2010 Excel that calculates set commission amounts ($100,$150,$200,&$250) for various dollar sales amounts?

I have been able to work it this far:

=IF(D4>2999,D4<4000)*("$200.00")

The part I need to know is how do I expand this formula to include the next level of commission pay off $250.00

How close am I?

=IF(D4>2999,D4<4000)*("$200.00") (D4>=4000)*("$250.00")

I am not sure how I join or add the brackets together.

I appreciate you help.

use nested IFs

so

IF( test , true , false )

now to nest in the false section (you can also nest in true)

IF( test, true, IF(test, true, false) )

if you want to give the details , i can set up

=IF(D4>2999,D4<4000)*("$200.00")Click to expand...

not sure why thats works

I would also look at the structure of the data and you may avoid using the between

but for between use

AND( D4>2999,D4<4000)

as the test

but you can often start with the greatest value

IF( D4 > 4000, "4001+ commision calc", if(d4> 2999, "3000+ commision calc", if( d4>1000, "1001+ commision calc", "value for below or equal 1000")

if you want to include greater or =

>=

the tests are carried out in the order written

so if its false for >4000 - then the next test shows below 4001 but greater than 2999

I am looking to check and entire row of data up to (200 entries)

For number that is less than one cell and greater then another cell

then enter the value of the cell if yes and skip if no creating a list

I am using excel 2000

Any ideas?

Description:

If cell A2=US or if cell A2=CA then return NALO, if not then return INTL. I have the following formulas and just need to merge using the OR statement I think?

=If(A2="US", "NALO", "INTL")

=If(A2="CA", "NALO","INTL")

Thanks,

Ben

Hi,

I want to be able to get excel to return a total based on adjacent cells with the returned formula being the lowest number in the other cells.

i.e. cell A1 will display the lowest number of cells A2 to A20 which will all have different numbers

Is this possible? If so can someone give me the formula

thanks in advance

Lee_1133

Hi

I have this excel sheet with several tabs one for each week (1,2,3 etc)

Each week I copy last week's tab and rename it with this week's number. My problem is that I have some formulas in the sheet that takes one cell and subtract from the same cell the week before e.g

=A1-2!A1 (A1 being week 3 and subtract with 2!A1 being week 2)

But when I copy the sheet from last week (week 3) I this formula will be wrong since I need it to be:

=A1-3!A1

Is it any way that I can set up the formula without having to go into all the formulas and change it from 3! to 4!?

I hope this was understandable!

Thanks!

Re: "having to go into all the formulas" -- have you tried Replace (CTRL+H) to replace 3! on the new sheet with 4!?

HTH

(EDIT: welcome to the board)

I need a formula that will lookup between 2 given date ranges and return a sum of numbers in a specific column. For example, I want to sum all dividends for symbol EEF in between the dates of 12/29/2006 and 3/13/2007. I have a sheet set up with all symbols, dates, and dividends that are needed, but i can't figure out a lookup function to only return the values over a given range of time. I hope this makes sense. Thank You

Brandon

the word problem is:

1 per 25 for the first 50 and 1 per 50 for the remainder exceding 50

=IF(C11/B11>50,((50/25)+(C11/B11-50)/50),(C11/B11)/25)

above is the formula i have, it is not correct, c11/b11 if the number from which this formula is calculated

the other word problem is:

1 per 40 for the first 80 and 1 per 80 for the remainder exceeding 80

=IF(C11/B11>80,((80/40)+(C11/B11-80)/80),(C11/B11)/40)

above is the formula I have, it is also not correct

c11/b11 if the number from which this formula is calculated.

If i do the math manually i do not get the same answers.

I am really excel illeterate.

Please help, and Thank you!

the word problem is:

1 per 25 for the first 50 and 1 per 50 for the remainder exceding 50

=IF(C11/B11>50,((50/25)+(C11/B11-50)/50),(C11/B11)/25)

above is the formula i have, it is not correct c11/b11 if the number from which this formula is calculated

the other word problem is:

1 per 40 for the first 80 and 1 per 80 for the remainder exceeding 80

Closing duplicate to: http://forums.techguy.org/business-applications/616873-need-excel-formula.html

Please only submit one post per question.

Hi I need to know a formula that will combine the data in two cells into one cell.

Example:

A2 has "Missing" in it and B2 has "Lamp" in it. I want C2 to combine A2 & B2 and say Missing Lamp.

Thank you.

I am trying to create a formula in excel with great difficulty.

The files has 3 tabs. I am trying to take from the first tab called tracking. Here is what I need:

count if d3:d999="Ace" and e3:e999="Damage"

Now, it won't seem to do both functions. I only need the count if both are true within that range, so if D5="Ace" and E5 ="Damage" then count 1, but if both aren't true for the range, then not to count.

Any help?

irishgal said:

I am trying to create a formula in excel with great difficulty.

The files has 3 tabs. I am trying to take from the first tab called tracking. Here is what I need:

count if d3:d999="Ace" and e3:e999="Damage"

Now, it won't seem to do both functions. I only need the count if both are true within that range, so if D5="Ace" and E5 ="Damage" then count 1, but if both aren't true for the range, then not to count.

Any help?Click to expand...

Try,

=SUMPRODUCT(--(D3 : D999="Ace"),--(E3:E999="Damage"))

I am running Excel 2007, and I am trying to figure out how to write a formula for my payroll worksheet. I have attached a portion of my spread sheet. What I want to do is have L28 be just overtime, which is any time over 40 hours. If the RT is less than 40, I want this to read 0. I would also like for K28 to be calculated to be 40 hours, if more than 40 hours have been worked in that week. So if it is less than 40, it should read what that time is. Does that make sense? Any help would be greatly appreciated!

Thank you in advance.

Chris

i am trying to create a formula to multiply one cell with annother at the moment I have in cell H13 ='P+L'!H8

now I need to times this by cell d3 but everytime I enter a value in D3 it shows a nil value in H13

so H13 has an original value of 1285.9 ( a a result of the formula ='P+L'!H8)

Then is there is a value in D3 I want to times H13 by this value

so in D3 I have a value of 0.65 so the value in H13 should read 835.8

if no value in D3 then the value in H13 should stay at 1285.9

Cannot get it to work

Hey all,

I'm new here, so this may be a simple fix...

anyways, I was wondering how to adjust an excel document to expand a formula when you add a row?

Currently, I have used the COUNTA formula to count the number of occurrences in some cells. In the future I know that there are going to be new rows added. Now these won't be counted but they need to be...

Is there anyway to extend the COUNTA formula automatically?

A little background on the list.

So there are 14 names. And then six column headings with various topics. One of which is "Pay Period Missed" I need to be able to count the number of pay periods missed, per name (not all of the names have the same amount of pay periods missed). Currently I use the COUNTA formula, but this will become tricky once more Pay Periods are added next to the name...

Hi, welcome to the forum.

I think posting a sample file without sensitive data will help to explain what you want and it's good practice to also mention the version of Excel you're using.

I guess that what you want can be solved by means of a formula, but it's also possible that a macro will make it even simpler since it overcomes the problem of formula's being deleted by accident.

I attached a picture of the spreadsheet/current formula. I would like to add another formula into cell K101 so that when a value is entered into M101, K101 goes to 0.

I am using this formula:

IF(G1="O",FVSCHEDULE(B1,{-.0314772}),B1)

G1 does have "O"

B1 = 7.74

What I get is the number 0. I can't find any error. What am I missing?

If I just do a FVSCHEDULE(B1,{-.0314772}) it calculates properly.

I am using Excel 2000 on Windows XP Home Edition

If G1 is a number 0, then you don't put quotes around the 0 in the if function.

Quotes are normally used for text.

I no an IF statement will only consider 7 IFs, therefore, I need a formula that will process the following:

=IF >=151,"80", >=126,"70", >=101,"60", >=81,"50", >=61,"40", >41,"30",

>=31,"20", >=22,"15", >=16,"10" >=11,"5"

Example:

If the number considered is 23 ………. I need the results to show 15.

Thanks so much for your help!

Tammy

Really not good at maths. But I want to add a column of dollar value then add 10% to the total all in one cell.

Whether I use autosum or just add them manually then add the 10% I dont care but can not seem to get it to work.

This is another problem on another spread sheet I have a dollar value then to find out what that value would be + 10% I have to times it by 110% is this the only way to do this or ???????

The dollar value plus 10% should be multiplied by 1.10.

so to do that for cells A1 to A20 the formula would be

=sum(a1:a20)*1.10

Helllo,

I need to connect a sheet that shows my grades: percentages, and number of points the project is worth, and i need to connect a sheet that shows the grading scale, it looks like this..

100 A+

99 A+

98 A+

97 A

96 A

95 A

94 A-

93 A-

92 B+

91 B+

90 B+

89 B

88 B

87 B

86 B-

85 B-

84 C+

83 C+

82 C+

81 C

80 C

79 C

78 C-

77 C-

76 D+

75 D+

74 D

73 D

72 D

71 D-

70 D-

69 F

So i need to somehow put a formula into a cell that says because i got 100% on this project, (then it connects with the other sheet) it is an A+

Please Help! thank you in advance

Hello,

I am struggling to find a formula that will do the following:

Sheet 1 lists all of the schools in my school district, with multiple columns. For example,

A2 = Central Elementary

B2 = # Tested Math

C2 = # Passed Math

D2 = % Passed Math

E2 = # Tested Reading

Etc.

Sheet 2 lists some of the schools with data for Math

A2 = Central Elementary

B2 = # Tested

etc

Sheet 3 lists some of the schools with data for Reading

I need a formula that will look for "Central Elementary" in Sheet 2 and then the corresponding data for that school. I can't do a simple copy and paste because not every school is listed in sheets 2 & 3, but all schools are listed on sheet 1.

So, my formula needs to look for Central Elementary in Sheet 2 and then the data from that sheet.

Hope this makes sense.

Thanks!

Hi:

I have created a spread sheet to compare 2004 figures with 2005 figures -- with the results showing increase/decrease percentage.

The variance (column N & O) is set up to give me percentage amounts.

Sometimes the percentages are under 999% & some are well over 1000% --

which is fine -- that formula is working fine, but I want it to do more.

Here is what I want ........ if the % amount is over 1000% -- I just want

the cell to read "+1000%"... if it's under 1000%, I want it to read the

true value, whatever that might be between 1% up to 999%, because

sometimes the values are so large -- they just look stupid (ie 1670000%)--

just want to simplify it a little.

Here is the formula I tried: of course it doesn't work!! If it is over 1000% it reads +1000%, but if it is under - it reads FALSE instead of the true value.

=IF(L27/J27>=1000,"+ 1000%"),(IF((L27/J27<999,sum((L27/J27))

Can ya help????

Please see the Attachment, and open as an excel file.

Two formula is selecting the lowest price and then selecting the vendor of the lowest price.

So far if there is no price for one of the vendors for eg, the formula does not work, and if I put a zero in, it selects the zero as best price and vendor.

How can I tell the formula to select the best price but if it shows blank or zero to goto the next lowest price?

Hope I have explained it ok

I am trying to find the formula to total up a sum between certain dates.

for example

In column A i have the dates

In column B i have the amounts paid in

I need to be able to click into a cell and use a formula to calculate the total amount in column B between certain dates in Column A.

EG: the amount paid in between 01/10/2007 and including 30/09/2008

i have tried the following but no sum comes out, =SUMIF(A:A,">01/10/2007and<30/09/2008",B:B)

Please Please can anyone help

Kaly

I am not a very experienced Excel persion but I believe the problem is that the "criteria" argument used in the SumIF function is unable to combine logical expressions like you are trying to do:

Can Do ">6/30/2007" and can do "<11/1/2007"

But Can't do ">6/30/2007 AND <11/1/2007" at the same time

For you particular problem there still is a solution that uses the SumIf function

Add up ALL of the payments and then, using SumIf, subtract off those that fall before the start date and those that fall after then ending date

So to add all the payments between (including) June 1 '07 and Sept 30 '07:

Good:

=sum(B1:Bn) - sumif(A1:An,"<6/1/2007",B1:Bn) - sumif (A1:An,">9/30/2007",B1:Bn)

Now if you think about this approach you see that it isn't really necessary to sum all the payments and subtract off both ends, you only need to sum all the payments that fall on or after the start date and then subtract off those that fall after the end date.

Better:

=sumif(A1:An,">5/31/2007",B1:Bn) - sumif (A1:An,">9/30/2007",B1Bn)

How do you create a formula that sums items from every second column:

JanActual Jan Budget Jan Variance and so on through each month

Then I want columns at end that sum total actual, total budget, total variance

I figures there is a faster way than manually entering each actual cell # in a sum formula, but I don't know how.

You could use sumproduct.

If you have actuals in columns a1, d1, g1, etc., budgets in b1, e1, h1, etc. and variances on c1, f1, i1, etc. then:

=SUMPRODUCT(--(MOD(COLUMN(A1:AJ1),3)=1),A1:I1) will give total actuals for a twelvemonth

=SUMPRODUCT(--(MOD(COLUMN(A1:AJ1),3)=2),A1:I1) will give total budgets, and

=SUMPRODUCT(--(MOD(COLUMN(A1:AJ1),3)=0),A1:I1) will give total variances

Ok so I have my products in column B. In column H I have the price of each product. In column P, I want to be able to assign a value to each thing in column B and then add the total per month.

Product Month Price Total Product Sales by Month

Router JAN 899

Monitor JAN 219

Monitor JAN 219

Switch FEB 699

Etc.....Is there a formula for this?

I have a sheet that contains a list of numbers for each hour of each day, monday through sunday 1 to 24. I need a formula to calculate the average of those numbers for the full week and then I need to make a chart with those averages. Can anybody help?

How is your spreadsheet set out?

There is a simple average formula - but it all depends on the data it is looking at

Hello,

I was wondering if one of you Excel Gurus Could help me

I have tried some different formulas with the "if" command and what not but am Unable to do this. Basically when I copy a bunch of Asset tags into one Column (eg. G) I need to automatically filter the Desktops into another Column (eg. H). If any of the Numbers match the numbers in column G match any of the numbers in column A then have the filter remove the Numbers. Whatever is left put in Column H.

I know this is possible

I hope that is understandable

I have attached the Excel sheet in a zip file

Thanks in advance

Tim

How can I get this formula to leave blank if there is no value in H9?

=IF(H9=E9,E$8,IF(H9=F9,F$8,IF(H9=G9,G$8,"")))

When there is no value in E9, F9 and G9, it still pulls the value of E$8

Thanks

Hi I run a small sales team and need to set up a excel work sheet.

It is for the shift rotas. I need it to calculate hours works.

For example person works 9am to 5pm with half hour lunch. I need it to calculate hours work minus lunch time.Obviously there are various shift times etc any help would be very much appreciated.

Any suggestions...

This comes to mind, not sure if it is correct. I just put it together

A4 = Start

B4 = End

=ABS((A4*24)-(B4*24))

I am needing help with a formula

Formula is for cell d1

IF C1 IS BLANK

THEN LEAVE BLANK

IF C1 IS 0 OR GREATER THAN 0 THEN

B1-C1

Thanks for any help I can get.

Will C1 ever be less than zero? If not, this should work as the formula for d1.

=if(c1="","",b1-c1)

It looks at c1 and if c1 is blank, then return blank, otherwise return b1-c1.

Hope that helps.

All;

I'm working on a Excel template that was created by someone how left there is a formula that I'm haveing problmes identifying where the file is beening saved. Here is the formula: =IF(default_dir_admin="","",default_dir_admin) Basically I just what to allow users of the template to click on the save button and the file will save to the location in the default directory. Any help would be appreciated.

Hello there, welcome!

Some questions arise here. Is this an actual template file (.xlt)? If you only want the file saved to a specific directory, I suggest using an event trapping (before_save) to control it manually. This means some VBA.

Is that about what you were thinking?

I am doing a spreadsheet for a volunteer fire department and we want to be able to keep track of the percentage of meetings, drills, and fire calls each person attends. I thought I had it right but I guess not now. I've included what I have done so far.

My problem is I don't know how many members there is at the time so I just created enough for 47 members to be tracked (I'm sure it's less than that). I realize I have an error formula but after input is placed in cell it disapears. I do not want that error to show up if I haven't filled the cell (I don't want error printing out on sheet if there is no member listed for that row).

Also I would like someone to verify that I did things right at the bottom... Where I have Department Averages.

This is like my first spreadsheet I've done so be easy on me. Trial and error. Any suggestions would be appricated.

Thanks in advance,

Art

Filter the divisor with an IF function,

=IF(B6="","",C6/B6)

Hi there: What I am trying to do is set up an invoice. The invoice is complete and works well but I want to add a formula to calculate shipping and handling charges depending on the cost of the merchandise. There are 7 different levels for the charges depending on the cost. I have used nesting and it seems that I am only getting the first level charge no matter what the cost of the merchandise is. Are there any Excel experts out there who wouldn't mind giving me a hand? It would be greatly appreciated.

Thanks in heaps in advance.

Hi Guys

I need help with a formula please. I have been asked to analyse client satisfaction scores for my firm, and then analyse by each solicitor. I have managed to analyse the data as a whole giving average scores for each question, but then i need help breaking that down further, into scores per fee earner.

Basically, i am using 4 worksheets now, the main data entry being on worksheet 1. The solicitor analysis is worksheet 3. I would like to create a formula which will calculate the average score of question 1, per specified solicitor.

EG. =IF('Data Entry Sheet'!D2=2,AVERAGE('Data Entry Sheet'!F:F))

Column D being the column in which the solicitor is recorded, number 2 being a specified solicitor's identifying number, and column F being the score for question 1. So, this basically states that when cell D2 contains solicitor number 2, it gives me the average of ALL solicitors scores for question 1. However, i need the average of the scores solely for solicitor 2, of which there will be many instances. I also need to be able to extend the formula so it searches all of column D for solicitor number 2, not just Cell D2, but i couldn't get it to accept what i was trying to put! I would ideally like to say " When column D says solicitor 2, find the average of all scores for question 1 FOR THAT SOLICITOR ONLY!"

Baffled??!!Can anyone help, if they can figure out what i'm trying to do!!