Sum the products of a formulas

I have a gradebook that returns values using the "LOOKUP"
function for a 60 question multiple choice test. I need
to sum the product of the returned values. I can use the
paste as text, then convert the text to a numeric value,
but I have to perform this function for each cell, and I
have 14,000 cells.

Is there a way to setup this formula to return a value
that i can then sum? =LOOKUP(I60,{"a","b","c","d","e"},
{"0","1","0","0","0"} The formula returns a 0 or 1 for
each test question but when I try and sum the results all
I get is a 0.
Thanks,
Bill 0 1/29/2005 6:32:18 PM excel.misc  78881 articles. 5 followers. 2 Replies 363 Views Similar Articles

[PageSpeed] 13

=SUMPRODUCT(LOOKUP(Range,{"a","b","c","d","e"},{0,1,0,0,0})

Bill Harney wrote:
> I have a gradebook that returns values using the "LOOKUP"
> function for a 60 question multiple choice test. I need
> to sum the product of the returned values. I can use the
> paste as text, then convert the text to a numeric value,
> but I have to perform this function for each cell, and I
> have 14,000 cells.
>
> Is there a way to setup this formula to return a value
> that i can then sum? =LOOKUP(I60,{"a","b","c","d","e"},
> {"0","1","0","0","0"} The formula returns a 0 or 1 for
> each test question but when I try and sum the results all
> I get is a 0.
> Thanks,
> Bill 0 1/29/2005 6:55:48 PM
Correcting for a missing paren...

=SUMPRODUCT(LOOKUP(Range,{"a","b","c","d","e"},{0,1,0,0,0}))

> =SUMPRODUCT(LOOKUP(Range,{"a","b","c","d","e"},{0,1,0,0,0})
>
> Bill Harney wrote:
>
>> I have a gradebook that returns values using the "LOOKUP" function for
>> a 60 question multiple choice test. I need to sum the product of the
>> returned values. I can use the paste as text, then convert the text to
>> a numeric value, but I have to perform this function for each cell,
>> and I have 14,000 cells.
>> Is there a way to setup this formula to return a value that i can then
>> sum? =LOOKUP(I60,{"a","b","c","d","e"},
>> {"0","1","0","0","0"} The formula returns a 0 or 1 for each test
>> question but when I try and sum the results all I get is a 0.
>> Thanks,
>> Bill 0 1/29/2005 6:57:32 PM Similar Artilces:

lock formulas
Is there a way to lock the formulas on a series of linked excel spreadsheets so the formulas can't accidentally be deleted? I have it set up so that 4 identical spreadsheets with 12 tabs feed into one other spreadsheet and want to lock all the formulas. Is there an easy way to do this? Thanks! Dooma You can select which cells to lock and then enable worksheet protection. By default all cells on a sheet are locked when sheet protection is enabled. Hit CRTL + A(twice for 2003 users) to select all cells. Format>Cells>Protection. Uncheck "locked" and OK out. Now s...

IIF formula
I have a formula in a query which works fine until I tried to test it by deleting values in the form. Instead of nothing showing up, like how the formula should work, it showed ",X" The formula Other Fills: IIf([Other]=0,Null,[Other] & "X" & [Type]) & IIf([Other 2]=0,Null,"," & [Other 2] & "X" & [Type 2]) Example: [Other] = 2 [Type] = 4 [Other 2] = 3 [Type 2] = 5 Should look like 2X4,3X5 When [Other 2] and [Type 2] values are deleted it should look like 2X4 instead of 2X4,X The default value for the [Other] and [Other 2]...

Formula ErrorHi
...

how do I sum fields down through a fixed field value.
Hey, I am trying to fill fields down by auto sum fields down. Ie. selecting A10 through 20 where I am inserting value from cell B10. What happens when I auto sum is that it calculates each field down, i.e. A10, A11 and so on. What do you mean by "auto sum fields down"? And "inserting value from B10" You want to add the value of B10 to each of A10:A20? Copy B10, select A10:A20 and paste special>add>ok>esc. Gord Dibben MS Excel MVP On Mon, 23 Nov 2009 13:45:01 -0800, rgisla <rgisla@discussions.microsoft.com> wrote: >Hey, I am ...

Sum value between two dates and copy to new cell
Hi Guys, Thanks for all the previous helps and guidance on excel sheet and im looking forward for new assistants. i've various dates in column A and values in column B. These values should sum and copy to column E according to date between column C & D, example as below, A B C D E Total 04/01/2010 12 01/01/2010 28/02/2010 02/04/2010 133 01/03/2010 30/04/2010 05/06/2010 154 01/05/2010 30/06/2010 03/04/2010 112 01/07/2010 31/08/2010 05/05/2010 65 01/09/2010 31/10/2010 06/07/2010 34 01/11/2010 31/12/2010 02/01/2010 26 02/0...

Summing the last 200 days
Hi, I was wondering if anyone could help me - I need to add up the 200 most recent days for a spreadsheet and was wondering rather than messing with cut and paste if there was a formula that could do it all for me. I will have more than 200 days worth of info. Also the sheet I am adding up on has gaps between the weeks so its not just a matter of copying the formula over. Thanks! Louja, If you want the last 200 days of data - assuming one data point per day: =SUMIF(A1:A1000, ">=" & LARGE(A1:A1000,200), B1:B1000) Or within the last 200 days: =SUMIF...

need help with formula #2
how to write a formula? if cell =>24 multiple by 22. if cell is <24, multiply 24 by 22, multiply difference by 33 add together -- Kerri Where do I find the difference "Kerri Olsen" wrote: > how to write a formula? > if cell =>24 multiple by 22. if cell is <24, multiply 24 by 22, multiply > difference by 33 add together > -- > Kerri See Help for "IF worksheet function" I can't be more specific without a better description of the alternative calculations. Jerry "Kerri Olsen" wrote: > how to write a formula? > if cell ...

Missing Sum at Bottom Right Corner
When I highlight a few cells, the sum of the numbers should show up i the bottom right corner of my screen. However, this is not appearing. How do I turn this feature on -- Ramro ----------------------------------------------------------------------- Ramrod's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2689 View this thread: http://www.excelforum.com/showthread.php?threadid=47083 Make sure the status bar is turned on: tools|options|View tab|in the Show Section. Make sure that the statusbar is visible. window|arrange|tiled (and resize by hand) rightclick ...

Need help w/ formula
Is there a formula that will change another cells information w/o having a formula in that particular cell. For instance, the IF(N5=Removed" then J5=0) without putting a formula in j5? No. A cell formula cannot change another cell. You can write code to react to value changes in cell N5 and the *code* can change the value in J5 "Todd Nelson" wrote: > Is there a formula that will change another cells information w/o having a > formula in that particular cell. For instance, the IF(N5=Removed" then J5=0) > without putting a formula in j5? No, you cannot ...

I am a manager in a big office where agents log into their phones to get calls. We keep track of how many minutes an agent is "logged off" of their phone system. I would like to be able to have a formula to use in Excel to speed up the calculations for this info. Here is what the info looks like that I am given and what I need to calculate: Sunday, December 03, 2006 Agent Login Time Logout Time # of minutes logged out Doe, Jane 6:58am 8:51am (need to find # minutes from 9:39am-8:51am) Doe, Jane 9:39am ...

Formula Help 02-02-10
How do I enter a formula to look at a number and based on calculation round up or down to nearest hundreth? For example 421 would round down to 400 while 573 would round up to 600. try =round(a1,-2) where A1 has the number you want to round to the 100's "Jackie Morin" wrote: > How do I enter a formula to look at a number and based on calculation round > up or down to nearest hundreth? For example 421 would round down to 400 while > 573 would round up to 600. You could look up ROUND in Excel help. Try =ROUND(A2,-2) -- David Biddulph Jackie Morin wr...

Help looking up the last product in a container
I have a data base that tells production what porduct to put in what trailer and I need to be able to find out what the last product was in the trailer. All this information is in the table on the last trailer by product name, trailer number and date returned. Can someone help me with this? -- Roger Perkins rperk60@swbell.net Use DLookup() to get the previous product. This example assumes a table named tblLoad, with fields like this: TrailerID Number what trailer is used for the load. ProductID Number what product was carried. LoadDate Date/Time when this...

Excel not pasting the formula or reference
I am trying to take a worksheet "apple" and copy it to a new workbook "banana". It works for another worksheet but not this one. I am highlighting the whole worksheet and copying it and then opening the workbook sheet and clicking the corner box and selecting paste. When it works correctly the cell would look like this: cell from "apple" ='sheet1'!A2 then when it is pasted over to "banana" ='C:\Documents and Settings\Desktop\PERSONAL\[apple.xls]Sheet1'!A2 now when i paste it over it just pastes what the cell displays which...

Formula for > but <
For excel sheet, is there any other way than the formula I put in cel B1 as: =If(AND(A1>100,A1<200),"Yes","No") i.e. I want to detect the value in A1 if it is >100 but <200 -- Message posted from http://www.ExcelForum.com i what's wrong with this kind of formula. Not really a beter way available. You may try =If((A1>100)*(A1<200),"Yes","No") >-----Original Message----- >For excel sheet, is there any other way than the formula I put in cell >B1 as: >=If(AND(A1>100,A1<200),"Yes","No") >i....

VB6 formula not working in excel
Hi, I have a few formulas embedded in my vb6 code which calculate and enter the results in the excel sheet. These formulas work for me and everyone at my workplace. But for some reason these formulas have recently stopped working for some of our model users in other countries. I noted that the problem is only with the formulas that are calculating the percentage, the rest of the formulas are working on the sheet. I also wanted to ask, is there a difference in using N/A and N?A in VB6? I should probably ask this question in the VB forum, but if anyone knows, please advise. Thanks, ...

Inserting rows into sheet that contains a formula
I have a typical financial sheet where the columns sum (sum(a5:a15)) and the rows sum (sum(a5:h5)). The summation columns are locked and the sheet protected. Is there a technique whereby I can have the user insert a row which will keep the formats and row sum as well as changing the formula in the column sums? Thanks. Rick Hi Rick, The following method is not foolproof but generally it works OK. Insert a blank row between the bottom of data to be summed and the row with the sum formulas. Include the blank row in the sum formula. Protect the blank row along with the formulas and what...

Adding sums in rows and columns and colouring cells with conditions
Hi all, I have a long table where i need to add and colour individual cells based on 'days home' and 'days away'. The table is uploaded here https://www.yousendit.com/download/MnFqaUNBUzhoMlYzZUE9PQ In order to automate some of this i'd like to automate some, but preferably all aspects of this, such as: 1. automatically colour the cell orange for "home" and yellow for "away" 2. add separately in the home and away columns the number of days for each 3. add the rows for the alternate lines (ie days only, rates only) I want to do this without yet another...

Formula Help
I have first names in one column and last names in a second column and I need to combine them in one column for an import. The formula I usually use is =A1&" "&B1 but for some reason it doesn't want to work for this spreadsheet. It's Office 2007 and I've saved the file as a regular worksheet. Any ideas? Never mind, it's just something with this particular spreadsheet. I copied and pasted the columns into a new one and it worked. "Brittany" wrote: > I have first names in one column and last names in a second column and I need >...

cell formulas #2
I am creating a spreadsheet for my acount payables and recievables. I want to designate a cell to keep a total. I need one cell whenever I add a figure it add to the total and another cell whenever I subtract a figure it will subtract from that same cell. I don't know if I understand exactly what you're asking. Might this be it? Enter this is B1: =SUM(A1:A100,C1,-D1) Where your total cell (B1), which is adding Column A, will increase by the number you enter in C1, and decrease by the number you enter in D1. -- HTH, RD -----------------------------------------------------------...

Year formula 11-30-09
I have a row of birth dates on a sheet, and need a formula to determine how many are over 60 years old. The cell format for the dates is 11/30/2009 Try =IF(DATEDIF(A1,TODAY(),"y")>=60,"60 or above","") OR year to year comparison =IF(YEAR(TODAY())-YEAR(A1)>=60,"60 or above","") -- Jacob "Robert" wrote: > I have a row of birth dates on a sheet, and need a formula to determine how > many are over 60 years old. > > The cell format for the dates is 11/30/2009 > Assume your dates are ...

Formula needed #2
Right all you clever people, I need a formula for my tax spreadsheet who can help me? I'm doing my mileage. I can claim back 40p a mile for the first 4,00 miles, then 25p a mile after that. I've been using this formula for the 40p a mile: =D75*0.4 (D75 bein the "total miles" cell) Fairly straightforward, but how do I make it so it adds 0.4 for th first 4,000 then 0.25 for everything after that. Is this possible? Thanks in advance -- dancingbear7 ----------------------------------------------------------------------- dancingbear72's Profile: http://www.excelforum.co...

How to succesfully sort column of formulas?
I am trying to sort a column of formulas that consist of cel references. Example: Column: Cell 1 holds: =D10 Cell 2 holds: =D11 Cell 3 holds: =D12 Data: D10=1 D11=2 D12=3 When I try to sort the column of formulas, the formulas change cel references to other cells and mess up the data. How can I sort a column of cells of which all of them contain formula and have the cells keep the references to the right place -- Message posted from http://www.ExcelForum.com Hi- If you write your formulae with absolute references rather tha relative references, you can sort them. Formulae should look ...

Help to create a formulae
I have 5 sheets, Heath, Gary, John and Fred. They go to their sheet Heading: Date Question1 Question2 Question3 They ent 3/3 5 6 1 They enter a date for each monday its filled out. then enter a number between 0 and 10. I have a mastersheet. It has the same heading as above but with the sheet name in column b What i would like is in the master sheet is if in column4 row1 cell a date. How can i change the date in col 4 row 1 that when it looks at the other sheets it will display only the figures for that date? Hope you understan...

Changing series formula
Hello, Im using the WorksheetFunction.Substitute code to change a series formula. But when a series sources data which is #NA, the code wont work, as the data is not visible on the chart. Anyway around this to change those series also? many thanks ahead. Ali - Convert the series type to xlArea first, then you'll be able to access the series. - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Ali wrote: > Hello, > > Im using the WorksheetFunction.Substitute code to change a series formula. But when a series sources ...

formula only works when file is saved.
For some strange reason my formulas do not work until I save the sheet.I am using 2007 with a file that was in 2003. Never had a problem before. It is in compatibility mode. when I enter the data nothing happens in the cell where the result is suppose to go. If I close and save the result shows up. If I open the file back up and take out the data I put in the result is still there, where it should be a zero. Something is screwed up. Multiple people have access to this file. Sorry about the post . I found the problem. Somebody changed calculations to manual "bob" <...