Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A difficult conditonal calculation
A difficult conditonal calculation
Hi. It's the look of my Excel file: Item 1----Amount Category---Details: ... Item 2----Amount Category---Details: ... Item 3----Amount Category---Details: ... Item 4----Amount Category---Details: ... As you see: - A1 is the item's name; A2 is the price/amount/cost/value of the item - B1 is the category the item belongs to; B2 is the description of the item. Now I would like to do some math which are the following: 1) If the category type is income, add all of them 2) If the category type is one-off expense, add all of them & put a minus sign in front of the value 3) If the category type is daily expense, add all of them & times 30, & put a minus sign in front of the value 4) If the category type is Asset A, put each amount of the item in this calculation: (Amount-1000)*3/5 and so on How can I achieve this? Thanks a lot. -- Additional information: - I'm using Office XP - I'm using Windows XP |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A difficult conditonal calculation
On Sun, 27 Nov 2005 19:57:57 -0000, "0-0 Wai Wai ^-^" wrote:
A difficult conditonal calculation Hi. It's the look of my Excel file: Item 1----Amount Category---Details: ... Item 2----Amount Category---Details: ... Item 3----Amount Category---Details: ... Item 4----Amount Category---Details: ... As you see: - A1 is the item's name; A2 is the price/amount/cost/value of the item - B1 is the category the item belongs to; B2 is the description of the item. Now I would like to do some math which are the following: 1) If the category type is income, add all of them 2) If the category type is one-off expense, add all of them & put a minus sign in front of the value 3) If the category type is daily expense, add all of them & times 30, & put a minus sign in front of the value 4) If the category type is Asset A, put each amount of the item in this calculation: (Amount-1000)*3/5 and so on How can I achieve this? Thanks a lot. It would probably be simpler if all of your data were in the same row. However, with your presently described layout, assuming your table is in A1:Bn then the formula to sum an individual category is: =SUMIF($A$2:$A$n,CategoryType,$B$1:$B$n-1) or, if you have 1,000 rows of data: =SUMIF($A$2:$A$1000,CategoryType,$B$1:$B$999) From there it is easy: Income: =SUMIF($A$2:$A$n,"Income",$B$1:$B$n-1) One-Off: =-SUMIF($A$2:$A$n,"One-Off",$B$1:$B$n-1) Daily: =-30*SUMIF($A$2:$A$n,"Daily",$B$1:$B$n-1) Asset A: =(SUMIF($A$2:$A$1000,"Asset A",$B$1:$B$999) - 1000*COUNTIF($A$2:$A$1000,"Asset A"))*3/5 --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple Calculation - but Difficult! | Excel Discussion (Misc queries) | |||
difficult calculation | Excel Worksheet Functions | |||
Calculation Setting in Excel | Excel Discussion (Misc queries) | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
Difficult (for me) formula/UDF calculation | Excel Worksheet Functions |