how to add 3 criteria for bullish,bearish,sideway in excel formula?

G

gc7sdu

New Member
Joined
Apr 15, 2024
Messages
14
Office Version
  1. 2021
Platform
  1. Windows
i got below formula for 2 criteria but cannot get in 3 criteria. i want to set (bullish, bearish, side way) condition using excel formula. appreciate your advise.
=IF(OR(AND(G134>="up",H134<="down"),AND(G134<="down",H134>="up")),"bearish","sideway")

Thank you
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
use a nested if - whats the criteria for 'Bullish"

=IF(OR(AND(G134>="up",H134<="down"),AND(G134<="down",H134>="up")),"bearish",IF( new criteria , "Bullish,"sideway"))

not sure on the use of > or < for text
=IF(OR(AND(G134="up",H134="down"),AND(G134="down",H134="up")),"bearish",IF( new criteria , "Bullish,"sideway"))

Therefore -

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.
www.mrexcel.com

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...
www.mrexcel.com www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.
www.mrexcel.com

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days
www.mrexcel.com www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone
 
Upvote 0
etaf said:
use a nested if - whats the criteria for 'Bullish"

=IF(OR(AND(G134>="up",H134<="down"),AND(G134<="down",H134>="up")),"bearish",IF( new criteria , "Bullish,"sideway"))

not sure on the use of > or < for text
=IF(OR(AND(G134="up",H134="down"),AND(G134="down",H134="up")),"bearish",IF( new criteria , "Bullish,"sideway"))

Therefore -

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.
www.mrexcel.com

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...
www.mrexcel.com www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.
www.mrexcel.com

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days
www.mrexcel.com www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone
Click to expand...
Thanks Sir, bullish criteria is g134>=up, ,h134>=up, "bullish"
 
Upvote 0
=IF(OR(AND(G134="up",H134="down"),AND(G134="down",H134="up")),"bearish",IF(AND(G134="up",H134="up"),"Bullish","sideway"))
not sure why you are using > or < for text
 
Upvote 0
etaf said:
=IF(OR(AND(G134="up",H134="down"),AND(G134="down",H134="up")),"bearish",IF(AND(G134="up",H134="up"),"Bullish","sideway"))
not sure why you are using > or < for text
Click to expand...
If not using > or<, it was showed error.
Your formula above not worked.

My criteria are
Bullish, g134=up,h134=up
Bearish,g134=down, h134=down
Side way, g134=down/up,h134=up/down
 
Upvote 0
isnt that different to the sample
=IF(OR(AND(G134>="up",H134<="down"),AND(G134<="down",H134>="up")),"bearish","sideway")
Click to expand...
up/down or down/up = Bearish

this si the old critera base on formula
Book2
DEFGH
133
134sideway
135Bullishupup
136bearishupdown
137bearishdownup
138sidewaydowndown
Sheet1
Cell Formulas
RangeFormula
E134:E138E134=IF(OR(AND(G134="up",H134="down"),AND(G134="down",H134="up")),"bearish",IF(AND(G134="up",H134="up"),"Bullish","sideway"))



new formula
=IF(OR(AND(G134="up",H134="down"),AND(G134="down",H134="up")),"sideway",IF(AND(G134="up",H134="up"),"Bullish",IF(AND(G134="down",H134="down"),"bearest","")))
based on
Bullish, g134=up,h134=up
Bearish,g134=down, h134=down
Side way, g134=down/up,h134=up/down

Book2
EFGH
134 
135Bullishupup
136sidewayupdown
137sidewaydownup
138bearestdowndown
Sheet1
Cell Formulas
RangeFormula
E134:E138E134=IF(OR(AND(G134="up",H134="down"),AND(G134="down",H134="up")),"sideway",IF(AND(G134="up",H134="up"),"Bullish",IF(AND(G134="down",H134="down"),"bearest","")))
 
Upvote 0
etaf said:
etaf said:
isnt that different to the sample

up/down or down/up = Bearish

this si the old critera base on formula
Book2
DEFGH
133
134sideway
135Bullishupup
136bearishupdown
137bearishdownup
138sidewaydowndown
Sheet1
Cell Formulas
RangeFormula
E134:E138E134=IF(OR(AND(G134="up",H134="down"),AND(G134="down",H134="up")),"bearish",IF(AND(G134="up",H134="up"),"Bullish","sideway"))



new formula
=IF(OR(AND(G134="up",H134="down"),AND(G134="down",H134="up")),"sideway",IF(AND(G134="up",H134="up"),"Bullish",IF(AND(G134="down",H134="down"),"bearest","")))
based on
Bullish, g134=up,h134=up
Bearish,g134=down, h134=down
Side way, g134=down/up,h134=up/down

Book2
EFGH
134 
135Bullishupup
136sidewayupdown
137sidewaydownup
138bearestdowndown
Sheet1
Cell Formulas
RangeFormula
E134:E138E134=IF(OR(AND(G134="up",H134="down"),AND(G134="down",H134="up")),"sideway",IF(AND(G134="up",H134="up"),"Bullish",IF(AND(G134="down",H134="down"),"bearest","")))
Click to expand...

isnt that different to the sample

up/down or down/up = Bearish

this si the old critera base on formula
Book2
DEFGH
133
134sideway
135Bullishupup
136bearishupdown
137bearishdownup
138sidewaydowndown
Sheet1
Cell Formulas
RangeFormula
E134:E138E134=IF(OR(AND(G134="up",H134="down"),AND(G134="down",H134="up")),"bearish",IF(AND(G134="up",H134="up"),"Bullish","sideway"))



new formula
=IF(OR(AND(G134="up",H134="down"),AND(G134="down",H134="up")),"sideway",IF(AND(G134="up",H134="up"),"Bullish",IF(AND(G134="down",H134="down"),"bearest","")))
based on
Bullish, g134=up,h134=up
Bearish,g134=down, h134=down
Side way, g134=down/up,h134=up/down

Sir, excel showing your formula inconsistent!?
Click to expand...
May the criteria change as follows
Bullish,g134=up, h133=up
Bearish,g134=down,h133=down
Side way, g134=down/up,h133=up/down
 
Upvote 0
you have now introduced row 133 - is that what you mean

same formula structure - JUST change H134 to H133
=IF(OR(AND(G134="up",H133="down"),AND(G134="down",H133="up")),"sideway",IF(AND(G134="up",H133="up"),"Bullish",IF(AND(G134="down",H133="down"),"bearest","")))
 
Upvote 0
How about this?
PriceChangeFinal.xlsb
EFGHI
134Bullishupup
135Side wayupdown
136Side waydownup
137Bearestdowndown
Sheet3
Cell Formulas
RangeFormula
E134:E137E134=IF(G134<>H134,"Side way",IF(G134="up","Bullish","Bearest"))
 
Upvote 0
etaf said:
you have now introduced row 133 - is that what you mean

same formula structure - JUST change H134 to H133
=IF(OR(AND(G134="up",H133="down"),AND(G134="down",H133="up")),"sideway",IF(AND(G134="up",H133="up"),"Bullish",IF(AND(G134="down",H133="down"),"bearest","")))
Click to expand...

But Excel template showing blank on this formula.

1713231794295.png
 

Attachments

  • 1713231728163.png
    1713231728163.png
    46.2 KB · Views: 1
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question
SUm up issues for criteria in single column (beginner user)
Replies
1
Views
120
Cubist
Cubist
J
  • Question
Conditional Formatting.. The bane of my Excel Existence
Replies
2
Views
52
RoryA
RoryA
T
  • Solved
=IF Formula Question
Replies
5
Views
278
Fluff
Fluff
T
  • Question
Pull data from multiple sheets that match varying criteria
Replies
0
Views
38
T1991
T
F
  • Question
Filter Formula W/ 3 Criteria (Leaving Blank will populate all)
Replies
2
Views
205
FiggyDad
F

Forum statistics

Threads
1,215,209
Messages
6,123,646
Members
449,111
Latest member
ghennedy

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top