logical functions

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

• Home
• Courses
• Promotions
• Schedule
• Formats
• Our Clients

Forum home » Delegate support and help forum » » Logical functions

# Logical functions

Resolved · High Priority · Version 2019

Janet has attended:
Excel Intermediate course

### Logical functions

In C3 I want to show Won or Lost. I have data in columns Q3 to T3 with values . I want to set up in Col V3 a cell to tell me:-
If Col C says Lost - show 0 if Col C shows Won - show the sum of Q3:T3.

It feels like this should be simple but I can't get it to work. Whatever I do I get a return of 0.
I have set up defined names of Lost and Won ( not sure if that was necessary)
I have tried this as an =IF which seemed the correct option.

Thank you

### RE: Logical functions

Hi Janet,

Thank you for your question. You're right, Excel can be very particular in what it wants from us for the correct outcome. For your data, the following IF statement works for me:

=IF(C3="Lost",0,SUM(Q3:T3))

To test for text, use = and ""
It's not case sensitive but spelling and spaces matter.
Also, when you "nest" a second function inside IF, make sure you put the opening and closing brackets in the right places.
Note that "Won" is implied in the Value if False.

If cell C3 (Won, Lost) is empty, you might want to write a nested IF statement to tell you it’s empty, otherwise Excel will simply return the Value if False:

=IF(C3="Lost",0,IF(C3=”Won”,SUM(Q3:T3),”Empty”))

I hope this helps.

Kind regards
Marius Barnard
STL

### RE: Logical functions

Thank you Marius, all working perfectly now.

### RE: Logical functions

Kind regards
Marius

Training courses

Training information:

 Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions What does 'Resolved' mean? Any suggestions, questions or comments? Please post in the Improve the forum thread.

### Naming and Using Constants

Constants make calculations easier so worksheets are more easily understood. Constant values also need to be given relevant and memorably names. It is also easier to change the value of a constant.

For example:
Instead of entering 17.5% in each cell when you generate a VAT amount you could name a Constant "VAT" and assigning a "0.175" value to it. To do this:

From the 'Insert' menu select 'Name', then select 'Define'.

Enter the constant

View all Excel hints and tips