Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas advanced
I am creating a worksheet and would like to use letters or specific numbers
(such as X=1,200 or 1=1,200. 2=800 and 3=400) to represent values. Mentor: Sally Sue 1 2 3 formula to reflect total amount Does anyone know how do to this? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas advanced
Patty,
If you used 3 = 1200, 2 = 800, and 1 = 400, you could use =SUM(B2:D2)*400 If you still want 1 = 1200 etc, then array enter (enter using Ctrl-Shift-Enter) =SUM((4-B2:D2)*400) If your values aren't so well organized, then you would need a different formula. HTH, Bernie MS Excel MVP "Patty Grimm" <Patty wrote in message ... I am creating a worksheet and would like to use letters or specific numbers (such as X=1,200 or 1=1,200. 2=800 and 3=400) to represent values. Mentor: Sally Sue 1 2 3 formula to reflect total amount Does anyone know how do to this? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas advanced
hi
your example is a tad confusing but maybe this will give you ideas... =if(c1=1,200,if(c1=2,800,if(c1=3,400,0))) if you want the cell to display nothing, replay the last zero with "". you can have up to 7 if's in 2003. 16 in 2007(i'm told) Regards FSt1 "Patty Grimm" wrote: I am creating a worksheet and would like to use letters or specific numbers (such as X=1,200 or 1=1,200. 2=800 and 3=400) to represent values. Mentor: Sally Sue 1 2 3 formula to reflect total amount Does anyone know how do to this? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas advanced
Hi,
You can use Defined Names or VLOOKUP, for example if you set up a table A B 1 X 1200 2 Y 800 3 Z 400 Then if cell D1 contains your letter enter the following formula in any cell =VLOOKUP(D1,A1:B3,2,False) ================= In the case of defined name you need to define each one - choose Insert, Name, Define and in the Names in workbook box enter X and in the Refers to box enter 1200. Click Add and repeat for each name. In the spreadsheet you just type =X in a cell and 1200 is displayed. If this helps, please click the Yes button. -- Thanks, Shane Devenshire "Patty Grimm" wrote: I am creating a worksheet and would like to use letters or specific numbers (such as X=1,200 or 1=1,200. 2=800 and 3=400) to represent values. Mentor: Sally Sue 1 2 3 formula to reflect total amount Does anyone know how do to this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced Criterea in Formulas | Excel Worksheet Functions | |||
Assistance with advanced formulas | Excel Worksheet Functions | |||
Advanced Conditional Formatting Ideas Needed! (ok, maybe not that advanced...) | Excel Discussion (Misc queries) | |||
advanced if????? | Excel Worksheet Functions | |||
Advanced Excel formulas | Excel Discussion (Misc queries) |