Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use different formula to calculation based on conditions
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.misc
|
|||
|
|||
Use different formula to calculation based on conditions
"0-0 Wai Wai ^-^" wrote:
It's the look of my Excel file: Item 1----Amount Category---Details: ... Item 2----Amount Category---Details: ... [....] - 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. What a screwy way to organize information in spreadsheet. Someone might have a solution for your arrangement. But it might be better to reorganize the spreadsheet in a more reasonable. Something like the following: Column A: Item Column B: Amount Column C: Category Column D: Description Row 1 for Item 1; row 2 for item 2; etc. That is probably not the answer you want to hear. But sometimes it is an important learn when you are using a tool fundamentally wrong or poorly. It will help you in the future, if not now. Then there are simple solutions to your problems, namely (assuming you have 10 items) .... 1) If the category type is income, add all of them =SUMIF(C1:C10,"Income",B1:B10) 2) If the category type is one-off expense, add all of them & put a minus sign in front of the value =-SUMIF(C1:C10,"One-off Expense",B1:B10) 3) If the category type is daily expense, add all of them & times 30, & put a minus sign in front of the value =-30*SUMIF(C1:C10,"Daily Expense",B1:B10) 4) If the category type is Asset A, put each amount of the item in this calculation: (Amount-1000)*3/5 =IF(C1="Asset A",(B1-1000)*3/5,"") I suspect you want something different for #4, but your description is not clear enough to know for sure. I suspect you meant to say that you want the sum of (AssetA Amount - 1000)*3/5. That would be: =(SUMIF(C1:C10,"Asset A",B1:B10) -1000*COUNTIF(C1:C10,"Asset A",B1:B10))*3/5 Hope that helps in some small way. It is not directly applicable to the organization of your data. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use different formula to calculation based on conditions
Errata ....
I wrote: =(SUMIF(C1:C10,"Asset A",B1:B10) -1000*COUNTIF(C1:C10,"Asset A",B1:B10))*3/5 Of course, that should be COUNTIF(C1:C10,"Asset A"). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is the IRR calculation based on cash flows at beginning of year? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Calculation based on a condition | Excel Worksheet Functions | |||
Formula to return cell contents based on multiple conditions | Excel Worksheet Functions | |||
formula based on format | Excel Discussion (Misc queries) |