Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
True or False
I have a list in a spreadsheet as follows. I want a formula to fill the Criteria column. The condition should be
a.. Invoice dollars should be less than $ 200 and b.. if there are two Invoices for the same customer and if the sum of all Invoices is still less than $ 200, it should reflect as "TRUE" for both Invoices. c.. If the dollar amount of an Individual Invoice or sum of all Invoices for a customer is greater than $ 200, it should reflect as "FALSE" Invoice Customer Sales Criteria W112311 CRE100 799.15 W112344 CRE100 185.42 W112345 DRW200 199.49 W112452 HLE300 129.95 W112457 HLE300 41.54 How can I accomplish the multiple conditions in one formula? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
True or False
"Krish" wrote in message
... I have a list in a spreadsheet as follows. I want a formula to fill the Criteria column. The condition should be Invoice dollars should be less than $ 200 and if there are two Invoices for the same customer and if the sum of all Invoices is still less than $ 200, it should reflect as "TRUE" for both Invoices. If the dollar amount of an Individual Invoice or sum of all Invoices for a customer is greater than $ 200, it should reflect as "FALSE" InvoiceCustomerSalesCriteria W112311CRE100799.15 W112344CRE100185.42 W112345DRW200199.49 W112452HLE300129.95 W112457HLE30041.54 How can I accomplish the multiple conditions in one formula? ---------------------------------------- Name Customers the Customer range Name Sales the Sales range =IF(SUMIF(Customers,Customers,Sales)<=200,TRUE,FAL SE) Ciao Bruno |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
True or False
"Krish" wrote:
How can I accomplish the multiple conditions in one formula? Yes. But the logic you describe below if unclear. I want a formula to fill the Criteria column. The condition should be a.. Invoice dollars should be less than $ 200 and Does this mean that if any invoice exceeds $200, you want FALSE? That seems to be redundant with #c. b.. if there are two Invoices for the same customer and if the sum of all Invoices is still less than $ 200, it should reflect as "TRUE" for both Invoices. Do you mean "at least two" or "exactly two"? And do you mean "less than or equal to"? Alternatively, see the 1st question for #c. c.. If the dollar amount of an Individual Invoice or sum of all Invoices for a customer is greater than $ 200, it should reflect as "FALSE" Do you mean "greater than or equal to"? Alternatively, see the 2nd question for #b. What about the condition when there is a single invoice (and sum of more than 2 invoices, depending on your answer to the question for #b above) for a customer, and it is less than $200? Do you want TRUE? Invoice Customer Sales Criteria W112311 CRE100 799.15 W112344 CRE100 185.42 W112345 DRW200 199.49 W112452 HLE300 129.95 W112457 HLE300 41.54 In the future, it would be ideal if you filled what you expect in the Criteria column, as an example. Assume the data above are in Columns A-D. There are at least two possible solutions, depending on your answers above. 1. In the simplest form, it seems to me that your condition reduce down to one: if the sum of all invoices for a customer is less than or equal to $200, TRUE; else FALSE. If you agree, put the following into D2 and copy down through D6 (tested). =IF(SUMIF($B$2:$B$6,B2,$C$2:$C$6)<=200,TRUE,FALSE) 2. To match the logic of your conditions, in case my understanding is wrong and to demonstrate the paradigm for handling "multiple conditions", put the following into D2 and copy down through D6 (untested). =IF(B2200,FALSE,IF(COUNTIF($B$2:$B$6,B2)=1,TRUE, IF(SUMIF($B$2:$B$6,B2,$C$2:$C$6)200,FALSE,TRUE)) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
True or False
Hi,
In the criteria column, enter the following formula I have worked with the following example. I have entered the data on names and amounts. True or false is the output. Ashish 1 True Ashish 5 True Mathur 3 True =IF(SUMIF($B$4:$B$6,B4,$C$4:$C$6)<200,"True","Fals e") Please adjust the example to yourt needs. Regards, "Krish" wrote: I have a list in a spreadsheet as follows. I want a formula to fill the Criteria column. The condition should be a.. Invoice dollars should be less than $ 200 and b.. if there are two Invoices for the same customer and if the sum of all Invoices is still less than $ 200, it should reflect as "TRUE" for both Invoices. c.. If the dollar amount of an Individual Invoice or sum of all Invoices for a customer is greater than $ 200, it should reflect as "FALSE" Invoice Customer Sales Criteria W112311 CRE100 799.15 W112344 CRE100 185.42 W112345 DRW200 199.49 W112452 HLE300 129.95 W112457 HLE300 41.54 How can I accomplish the multiple conditions in one formula? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inconsistent Macro Behavior | Excel Discussion (Misc queries) | |||
Help: runtime error - Method seriescollection object_chart failed | Charts and Charting in Excel | |||
Grouped Sheets and Formating | Excel Discussion (Misc queries) | |||
Adding True False Results | Excel Worksheet Functions | |||
Grand Totals @ Same Place | Excel Worksheet Functions |