Multiple If Statements

[milkandcookies]

Hi all,

I'd like to build a formula with multiple statements looking at multiple columns of data within its same row.

For sales rep "Bob", if column 'Period to Date' is =>25000, then it returns the text "PTD".
For sales rep "Bob", if column 'Beginning Balance" is =>15000 or >=-15000, it returns the text "BB".
For sales rep "Bob", if column 'Role' contains 'IVT', then it returns "ATTACK".

Ideally, the column would tell me each of these three results if Bob met criteria for each.
(e.g. "PTD, BB, ATTACK".) If "Bob" meets none of this criteria, the column would return blank.

If clarification is needed, please let me know!

Thank you!

 

[Hui]

Milk&Cookies

Firstly, Welcome to the Chandoo,.org Forums

There are some inconsistancies in your description
if column 'Beginning Balance" is =>15000 or >=-15000, I assume should be
if column 'Beginning Balance" is =>15000 or <=-15000

You use of the word Column, clouds wether you want the formula to apply to a row or to the whole column

So try:

If(Period to date >=25000, "PTD", if(or('Beginning balance'>=15000,'Beginning balance'<=-15000),"BB",if('Role'="IVT","Attack","Something else")))


I'd suggest supplying a sample file with a worked example, that way we can give a more targeted response

 

[NARAYANK991]

Hi ,

To add to what has already been posted , the following segment of a formula :

OR('Beginning balance'>=15000,'Beginning balance'<=-15000)

can be replaced by :

ABS('Beginning balance') >= 15000

if you think it makes for easier reading.

Narayan

 

[milkandcookies]

PERFECT! Thank you all so very much!