Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute text with assigned values
I have a table as below:
A B C D E 1 Value 2 9 5 Total 2 Item 1 x 3 Item 2 x x 4 Item 3 x 5 Item 4 Whereever x is plotted, I need the cell to be assigned values from the corresponding row above. Eg. Item 2 is marked x in col B, I want the cell B3 to have a value 2 even though x can be seen. Similarly item 2 is marked x in Col D, should now have a value 5. I need to sum up these values in the total column E. Do reply. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute text with assigned values
Perhaps something like this:
E2: =SUMIF(B2:D2,"x",B$1:D$1) copy that formula down as far as needed. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "TD" wrote: I have a table as below: A B C D E 1 Value 2 9 5 Total 2 Item 1 x 3 Item 2 x x 4 Item 3 x 5 Item 4 Whereever x is plotted, I need the cell to be assigned values from the corresponding row above. Eg. Item 2 is marked x in col B, I want the cell B3 to have a value 2 even though x can be seen. Similarly item 2 is marked x in Col D, should now have a value 5. I need to sum up these values in the total column E. Do reply. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute text with assigned values
If you want a result like the example below, you can use this formula
starting in E2 and copying down. =SUMPRODUCT((B2:D2="x")*$B$1:$D$1) A B C D E 1 Value 2 9 5 Total 2 Item 1 x 9 3 Item 2 x x 7 4 Item 3 x 9 5 Item 4 x 2 6 Item 5 x x 11 If you want to actually change the values of the cells with x's to the appropriate number, you will need a macro. Why don't you ask in the Excel-Programming forum? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Link Protected WorkBook ... and Get #N/A for Text Values! | Excel Discussion (Misc queries) | |||
Can anyone tell me how to hide rows that have 0 values and text. | Charts and Charting in Excel | |||
how to substitute a value for calculation with a column of values | Excel Worksheet Functions | |||
How to assign character (text) values to y-axis in a Excel chart? | Charts and Charting in Excel | |||
Help adding text values | Excel Worksheet Functions |