Excel Formula Issue - Aussie Stock Forums

1. ## Excel Formula Issue

I'm having an issue with one of my Excel formula. I have a column that is filled with text based on an If Function.

The Column looks like this:

-------------------------------------

Column Title (Direction)
Sideways
Up
Down

Sideways
Sideways

-------------------------------------

I want a Formula that will spit out the most common Text field, which would be Sideways in this instance.
I'm using this formula:
=INDEX(G4:G10,MODE(MATCH(G4:G10,G4:G10,0)))
It works for a normal text column that I tested but I think the If function may be stuffing it up because I only receive a #N/A (so no values input). The Cells are set to text BTW.

Cheers

2. ## Re: Excel Formula Issue

One suggestion is:

Add this formula to a cell: =COUNTIF(B5:B10,"=sideways")

It will count the cells which match the criteria in this case "sideways"
You can then add this formula to 2 other cells that count the "Up" and "Down" values instead.

This will give you totals for each category

Note: You will need to be aware of case sensitivity for the text you are searching.

Good luck

3. ## Re: Excel Formula Issue

Thanks mate! Done.

I used that formula to spit out the three totals for each criteria (up, down & sideways) which I put in cells S4, S5 & S6.

Then I used the below formula to find out the one with the highest mode and put it in a cell.

=IF(AND(S4>S5, S4>S6), "Sideways",IF(AND(S5>S4, S5>S6), "up",IF(AND(S6>S4, S6>S5), "Down","")))

Feels good to have a win

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
Aussie Stock Forums