#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Krish
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bruno Campanini
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ashish Mathur
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Inconsistent Macro Behavior Sarah K Excel Discussion (Misc queries) 4 November 1st 05 11:36 PM
Help: runtime error - Method seriescollection object_chart failed huangx06 Charts and Charting in Excel 3 July 9th 05 12:27 AM
Grouped Sheets and Formating Pank Mehta Excel Discussion (Misc queries) 3 March 24th 05 02:42 AM
Adding True False Results Arla M Excel Worksheet Functions 6 January 27th 05 07:29 PM
Grand Totals @ Same Place Amber M Excel Worksheet Functions 2 December 30th 04 08:13 PM


All times are GMT +1. The time now is 10:45 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"