Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel formula question
I am having trouble coding the correct formula. Here is an example of data
in worksheet1 A1 B1 C1 D1 E1 DATE value1 value2 Total1 Total2 03/01/2006 car 4dr 300 200 03/01/2006 car 2dr 200 100 03/01/2006 van 4dr 600 200 03/01/2006 van 2dr 300 100 03/02/2006 car 4dr 300 200 03/02/2006 car 2dr 200 100 03/02/2006 van 4dr 600 200 03/02/2006 van 2dr 300 100 In worksheet2 I have: A1 B1 Date Car-2dr-total2 03/01/2006 ? 03/02/2006 ? 03/03/2006 ? I need assistance is creating a formula in worksheet2 (b2) that will give me date =03/01/2006 and value1=car and vaule2=4dr equals total2. I hope this makes sense. Thank you . ..... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel formula question
If you are likely to want a total for 2dr cars, 4dr cars, 2dr vans and
4dr vans, I would suggest that on Sheet2 you include these parameters in the headings, i.e. put "car" in B1 and C1, "van" in D1 and E1, "2dr" in B2 and D2, and "4dr" in C2 and E2, and thus have your dates starting in A3. Enter this array* formula in B3: =SUM(IF((Sheet1!$A$2:$A$100=$A3)*(Sheet1!$B$2:$B$1 00=B$1)*(Sheet1!$C$2:$C$100=B$2),Sheet1!$E$2:$E$10 0,0)) * As this is an array formula, then once you have typed it in (or subsequently edit it) you need to use CTRL-SHIFT-ENTER (CSE) instead of just ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula - you should not type these yourself. Adjust the ranges to suit your data - I have assumed 100 rows. The formula can be copied across and down to suit the number of dates you have in Sheet2. You can make the formula more readable (and shorter) by using named ranges. In Sheet1 highlight all the data including the headings and Insert | Name | Create. Ensure that only Top Row is ticked in the Create Name box, then click okay - this will have created named ranges using the headings as names. The formula can then be written: =SUM(IF((DATE=$A3)*(value1=B$1)*(value2=B$2),Total 2,0)) Again, CSE to commit the formula, then copy across and down. Hope this helps. Pete Mona wrote: I am having trouble coding the correct formula. Here is an example of data in worksheet1 A1 B1 C1 D1 E1 DATE value1 value2 Total1 Total2 03/01/2006 car 4dr 300 200 03/01/2006 car 2dr 200 100 03/01/2006 van 4dr 600 200 03/01/2006 van 2dr 300 100 03/02/2006 car 4dr 300 200 03/02/2006 car 2dr 200 100 03/02/2006 van 4dr 600 200 03/02/2006 van 2dr 300 100 In worksheet2 I have: A1 B1 Date Car-2dr-total2 03/01/2006 ? 03/02/2006 ? 03/03/2006 ? I need assistance is creating a formula in worksheet2 (b2) that will give me date =03/01/2006 and value1=car and vaule2=4dr equals total2. I hope this makes sense. Thank you . .... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel formula question
see response at your other post.
"Mona" wrote: I am having trouble coding the correct formula. Here is an example of data in worksheet1 A1 B1 C1 D1 E1 DATE value1 value2 Total1 Total2 03/01/2006 car 4dr 300 200 03/01/2006 car 2dr 200 100 03/01/2006 van 4dr 600 200 03/01/2006 van 2dr 300 100 03/02/2006 car 4dr 300 200 03/02/2006 car 2dr 200 100 03/02/2006 van 4dr 600 200 03/02/2006 van 2dr 300 100 In worksheet2 I have: A1 B1 Date Car-2dr-total2 03/01/2006 ? 03/02/2006 ? 03/03/2006 ? I need assistance is creating a formula in worksheet2 (b2) that will give me date =03/01/2006 and value1=car and vaule2=4dr equals total2. I hope this makes sense. Thank you . .... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel formula question
your requirement, you must input data 3 Column, Date-value1-value2 equal to
Total2 .... you must add new column...Generate with formular [ =B3&C3&D3 ] Generate DATE value1 value2 Total1 Total2 38720car4dr 3/1/2006 car 4dr 300 200 38720car2dr 3/1/2006 car 2dr 200 100 38720van4dr 3/1/2006 van 4dr 600 200 38720van2dr 3/1/2006 van 2dr 300 100 38751car4dr 3/2/2006 car 4dr 300 200 38751car2dr 3/2/2006 car 2dr 200 100 38751van4dr 3/2/2006 van 4dr 600 200 38751van2dr 3/2/2006 van 2dr 300 100 38849box5dr 12/5/2006 box 5dr 400 600 38849box3dr 12/5/2006 box 3dr 200 700 and use function Vlookup(...) In worksheet2 at column Car-2dr-total2 Generate DATE value1 value2 Car-2dr-total2 38720car4dr 3/1/2006 car 4dr 200 38751van4dr 3/2/2006 van 4dr 200 38849box5dr 12/5/2006 box 5dr 600 =VLOOKUP(A11,Sheet5!A2:F10,6) ***test by*** - copy formularin column Generate,Car-2dr-total2 to next ROW - input Data in 3 Column - you Hide column Generate ------------------------------------------- "Pete_UK" เขียน: If you are likely to want a total for 2dr cars, 4dr cars, 2dr vans and 4dr vans, I would suggest that on Sheet2 you include these parameters in the headings, i.e. put "car" in B1 and C1, "van" in D1 and E1, "2dr" in B2 and D2, and "4dr" in C2 and E2, and thus have your dates starting in A3. Enter this array* formula in B3: =SUM(IF((Sheet1!$A$2:$A$100=$A3)*(Sheet1!$B$2:$B$1 00=B$1)*(Sheet1!$C$2:$C$100=B$2),Sheet1!$E$2:$E$10 0,0)) * As this is an array formula, then once you have typed it in (or subsequently edit it) you need to use CTRL-SHIFT-ENTER (CSE) instead of just ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula - you should not type these yourself. Adjust the ranges to suit your data - I have assumed 100 rows. The formula can be copied across and down to suit the number of dates you have in Sheet2. You can make the formula more readable (and shorter) by using named ranges. In Sheet1 highlight all the data including the headings and Insert | Name | Create. Ensure that only Top Row is ticked in the Create Name box, then click okay - this will have created named ranges using the headings as names. The formula can then be written: =SUM(IF((DATE=$A3)*(value1=B$1)*(value2=B$2),Total 2,0)) Again, CSE to commit the formula, then copy across and down. Hope this helps. Pete Mona wrote: I am having trouble coding the correct formula. Here is an example of data in worksheet1 A1 B1 C1 D1 E1 DATE value1 value2 Total1 Total2 03/01/2006 car 4dr 300 200 03/01/2006 car 2dr 200 100 03/01/2006 van 4dr 600 200 03/01/2006 van 2dr 300 100 03/02/2006 car 4dr 300 200 03/02/2006 car 2dr 200 100 03/02/2006 van 4dr 600 200 03/02/2006 van 2dr 300 100 In worksheet2 I have: A1 B1 Date Car-2dr-total2 03/01/2006 ? 03/02/2006 ? 03/03/2006 ? I need assistance is creating a formula in worksheet2 (b2) that will give me date =03/01/2006 and value1=car and vaule2=4dr equals total2. I hope this makes sense. Thank you . .... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Date Formula Question | Excel Discussion (Misc queries) | |||
excel division formula question | Excel Worksheet Functions | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Excel auto calculation formula question. | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |