Solved

MS Excel  "--" in Formula What is it doing?

Posted on 2016-11-25
3
27 Views
Last Modified: 2016-11-25
Thank you for looking at my question,

I have been sent a spreadsheet that contains a formula with syntax I've not seen before and hope somebody will explain it to me. The formula as it appears in the cell is below.

=SUMPRODUCT(--(DeliveryCountry=$A3)*(--(OrderOrigin="Sales")*(LEFT(Description,2)="FR")*--(LEFT(Type,3)="XL5"))*(Quantity))

Tell me please what does the double minus sign signify?
0
Comment
Question by:Crxfrd
  • 2
3 Comments
 
LVL 35

Accepted Solution

by:
Kimputer earned 500 total points
ID: 41901451
It's to coerce TRUE and FALSE values into 1's and 0's
0
 

Author Comment

by:Crxfrd
ID: 41901454
I found out what's happening now, it's acting like a 'WHERE' condition.

=SUMPRODUCT(--(DeliveryCountry=$A3)*(--(OrderOrigin="Sales")*(LEFT(Description,2)="FR")*--(LEFT(Type,3)="XL5"))*(Quantity))

SUMPRODUCT WHERE the content of the cell(s) in column DeliveryCountry =  the contents of ref A3 (in this case Germany)
                           WHERE the content of cell(s) in column OrderOrigin = "Sales" etc
0
 

Author Closing Comment

by:Crxfrd
ID: 41901456
Thank you
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now