help with If and formula

Samgraphics

Board Regular
Joined
Jan 9, 2022
Messages
52
Office Version
  1. 2011
Platform
  1. MacOS
HI,
So I’m trying to find a more efficient way to achieve this results. I used an if an function to see if the value in a cell is an odd or an even number and is less than ten. If it is it will highlight that cell with the color I choose. I made the column letter absolute so that I can copy the formula down the column. But I also want it to check that same column to see if the value is odd and greater than 10, is even and less than ten and is even and greater than ten and apply a specific background color for which ever is true. So that requires four different formulas. Not only that but I want it to do that for an array of 5 numbers, one in each column. So in total I have to write that formula 20 times for one row. I’m using excel’s conditional formatting to achieve this with the use formula to determine results option. The good thing is I can then copy it down to all the other arrays of numbers. I’m just worried that it will make excel run too slow since I have over 300,000 arrays of numbers.

In addition each cell has another formula that checks to see if the value is odd or even and less than or greater than 10 and adds a text string of odd or even in each each column using the if and formula.

Also in a separate two columns I’m using sumproduct(mod()) to check how many odds and evens there are in the row.

I tried to upload a minisheet but it didn't work on my mac.

Is there a more efficient way to accomplish this?

Thanks in advance
 

Attachments

  • Screen Shot 2022-01-16 at 8.36.51 PM.jpg
    Screen Shot 2022-01-16 at 8.36.51 PM.jpg
    102.3 KB · Views: 24

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
It's not clear what the end-game here is ...

You can conditionally format the entire range using only three formulae (plus a default background colour that will show only if the 4th option applies - I've used yellow below).

But is conditional formatting going to be of any practical use? The range is way too big to visualise, and you can't (readily) filter or search by cell colour.

Would it make more sense to convert to numeric values 1-4 so you can easily count, filter etc?

ABCDEFGHIJK
1N10
2
31Odd/Small
42Even/Small
53Odd/Large
64Even/Large
7
8SampleResult
91310313532131
1013141521034322
11587101812124
1215101215932431
139157161313143
141441371742313
159125151114133
16118871014212
17166818742241
Sheet1
Cell Formulas
RangeFormula
G9:K17G9=IF(ISODD(A9),1,2)+2*(A9>N)
Named Ranges
NameRefers ToCells
N=Sheet1!$B$1G9:K17
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A9:E17Expression=AND(ISODD(A9),A9<N)textNO
A9:E17Expression=A9<NtextNO
A9:E17Expression=ISODD(A9)textNO
 
Last edited:
Upvote 0
It's not clear what the end-game here is ...

You can conditionally format the entire range using only three formulae (plus a default background colour that will show only if the 4th option applies - I've used yellow below).

But is conditional formatting going to be of any practical use - the range is way too big to visualise, and you can't (readily) filter or search by cell colour.

Would it make more sense to convert to numeric values 1-4 so you can easily count, filter etc?

ABCDEFGHIJK
1N10
2
31Odd/Small
42Even/Small
53Odd/Large
64Even/Large
7
8SampleResult
91310313532131
1013141521034322
11587101812124
1215101215932431
139157161313143
141441371742313
159125151114133
16118871014212
17166818742241
Sheet1
Cell Formulas
RangeFormula
G9:K17G9=IF(ISODD(A9),1,2)+2*(A9>N)
Named Ranges
NameRefers ToCells
N=Sheet1!$B$1G9:K17
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A9:E17Expression=AND(ISODD(A9),A9<N)textNO
A9:E17Expression=A9<NtextNO
A9:E17Expression=ISODD(A9)textNO
Hi Stephen, thank you so much for the quick response and for the time in helping me out.

what you described here makes perfect sense. I understand the idea. The problem is I don't really understand the code and I get the conditional formatting part of it. The part I don't get is how to convert to numeric values. When I copied the code from the xl2bb I'm getting a #NAME error and I don't know how to fix it. I attached a screenshot. But what you said is what I'm looking for it's just that my excel skill level is subpar at best.
So what I'm trying to do is find out the number of odds/even in each row, and also return in separate columns whether the number in each cell is a low/high odd/even and color code them to represent it visually. I would like it to return a value just like you did in the example where it returned numbers that represent high/low odd/even but I'd like the conditional formatting to be in the returned cells and I'd like to change it from numbers to text. Numbers get jumbled up in my brain very easily that's why I was looking for a more visual representation of the data, it will also help with spotting patterns.

The end game here is that it's easier for me to visualize with colors and text since the numbers repeat so often sometimes my brain skips row and I end up mixing up the numbers. The numeric values for being able to filter search etc makes much more logical sense but like I said my excel skills are very poor.

I'm probably not making much sense here, sorry.
 

Attachments

  • Screen Shot 2022-01-17 at 5.59.33 PM.jpg
    Screen Shot 2022-01-17 at 5.59.33 PM.jpg
    82.7 KB · Views: 5
Upvote 0
When I copied the code from the xl2bb I'm getting a #NAME error and I don't know how to fix it.
XL2BB is not transferring the Named Range and @StephenCrump has one in his spreadsheet. This is what is causing the #NAME error.
Easiest way to fix it is
  • click on B2 (it has a value of 10 in the Cell).
  • Then in the address box in the top left corner where it says B2, type in N and hit return.
    All the #NAME references should disappear
(I wouldn't normally use such a cryptic range name but that is what is being used by the XL2BB worksheet)

1642421916686.png
 
Upvote 0
So perhaps more like this:

Cell Formulas
RangeFormula
G4:G12G4=SUMPRODUCT(MOD(A4:E4,2))
H4:H12H4=COLUMNS(A4:E4)-G4
J4:N12J4=IF(ISODD(A4),"ODD","EVEN")&"/" & IF(A4>$B$1,"LARGE","SMALL")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J4:N12Cell Value="EVEN/LARGE"textNO
J4:N12Cell Value="ODD/LARGE"textNO
J4:N12Cell Value="EVEN/SMALL"textNO
J4:N12Cell Value="ODD/SMALL"textNO


Thanks Alex for explaining the named range. I've used a cell reference $B$1 here instead.
 
Upvote 0
So perhaps more like this:

Cell Formulas
RangeFormula
G4:G12G4=SUMPRODUCT(MOD(A4:E4,2))
H4:H12H4=COLUMNS(A4:E4)-G4
J4:N12J4=IF(ISODD(A4),"ODD","EVEN")&"/" & IF(A4>$B$1,"LARGE","SMALL")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J4:N12Cell Value="EVEN/LARGE"textNO
J4:N12Cell Value="ODD/LARGE"textNO
J4:N12Cell Value="EVEN/SMALL"textNO
J4:N12Cell Value="ODD/SMALL"textNO


Thanks Alex for explaining the named range. I've used a cell reference $B$1 here instead.
Hi Stephen,
YES YES YES, that's exactly what I was looking for thank you so much. Huge help on this.
thanks so much.
Also, a big thank you to Alex also, your suggestion did solve the issue I was having with the error as well. Thank you.

There is just a small issue. For the conditional formatting, was I supposed to type in the formula (="EVEN/LARGE") "use formula to determine which cells to format" or select or "format only cells that contain" and select "specific text"? When I enter the formula above under "use formula to determine ...." nothing happens. No error but the formatting doesn't work. It only works when I select "specific text", which is ok. I was just wondering if I was doing something wrong.

However, the other codes work like a charm.
thanks again.

If I'm not being too much of a pain I'd like to ask for help with one other issue that just came up while I was using the very efficient code you gave me.

I was wondering if you could help.
I am trying to rate the numbers. Say for example a number has 1 low odd, 2 high odds, and 2 high evens I would like to put a ratting in the column next to it like "good" if I think that's a good combination or "bad" if I think it's a bad combination. So I'm trying to see if there is a way to check/filter the results to see how man odds/even and low/high in each 5 numbers or is that too much? Something like =IF(C2:E2="LOW ODD"*2&"HIGH ODD"*1&"HIGH EVEN"*2,"GOOD","BAD") I know I can't use SUMPRODUCT because the value in each cell is not a number but I'm lost as to how to solve this.

Thank you again for all your help.
 
Upvote 0
There is just a small issue. For the conditional formatting, was I supposed to type in the formula (="EVEN/LARGE") "use formula to determine which cells to format" or select or "format only cells that contain" and select "specific text"? When I enter the formula above under "use formula to determine ...." nothing happens. No error but the formatting doesn't work. It only works when I select "specific text", which is ok. I was just wondering if I was doing something wrong.
I did it this way:

1642659002283.png


Or you could use a formula: =J4="EVEN/LARGE"

I am trying to rate the numbers. Say for example a number has 1 low odd, 2 high odds, and 2 high evens I would like to put a ratting in the column next to it like "good" if I think that's a good combination or "bad" if I think it's a bad combination. So I'm trying to see if there is a way to check/filter the results to see how man odds/even and low/high in each 5 numbers or is that too much? Something like =IF(C2:E2="LOW ODD"*2&"HIGH ODD"*1&"HIGH EVEN"*2,"GOOD","BAD") I know I can't use SUMPRODUCT because the value in each cell is not a number but I'm lost as to how to solve this.
You can use the COUNTIF and/or COUNTIFS functions to count how many EVEN/LARGE's etc that you have.

If you let us know which combinations are GOOD and which are BAD, it should be easy to write an appropriate formula.
 
Upvote 0
I did it this way:

View attachment 55651

Or you could use a formula: =J4="EVEN/LARGE"


You can use the COUNTIF and/or COUNTIFS functions to count how many EVEN/LARGE's etc that you have.

If you let us know which combinations are GOOD and which are BAD, it should be easy to write an appropriate formula.
HI STEPHEN SO SORRY FOR THE LATE REPLY. I NEVER NOTICED THE NOTIFICATION IN MY INBOX. ANYWAY, I WAS ABLE TO FIGURE THAT PART OUT AFTER PLAYING AROUND FOR A BIT. THANK YOU SO MUCH. YOU'VE BEEN SUCH A BIG HELP.
I HAVE BEEN PLAYING AROUND WITH THE FORMULA YOU WROTE TO HELP ME UNDERSTAND IT BETTER AND I WAS WONDERING IF IT'S NOT TOO MUCH TROUBLE IF YOU COULD PLEASE HELP ME OUT SOME MORE.

I'VE ADJUSTED THE FORMULA A BIT TO THIS
=IF(C2>$AF$3,"HIGH","LOW")&" "&IF(C2>$AF$2,"HIGH","LOW")&" " & IF(ISODD(C2),"ODD","EVEN")
AS YOU CAN SEE I ADDED ANOTHER WORD AND CHANGED THE "/? TO A SPACE. WHAT I'M TRYING TO DO IS THIS, SEE SCREENGRAB.
NUMBERS THAT ARE LOWER THAN 10 ARE "LOW LOW + ODD/EVEN"
GREATER THAN 10 BUT LESS THAN 18 ARE "HIGH LOW + ODD/EVEN"
GREATER THAN 18 BUT LESS THAN 27 ARE "LOW HIGH + ODD/EVEN"
AND GREATER THAN 27 ARE "HIGH HIGH + ODD/EVEN"

I'VE ALREADY FIGURED OUT THE "LOW LOW" AND THE "HIGH LOW" BUT I CAN'T FIGURE OUT THE "HIGH HIGH" AND THE "LOW HIGH" I'M GUESSING IT NEEDS TO BE AN IF/OR STATEMENT?

PLEASE HELP ME.
THANK YOU SO MUCH
 

Attachments

  • Screen Shot 2022-01-22 at 3.30.13 PM.jpg
    Screen Shot 2022-01-22 at 3.30.13 PM.jpg
    44.6 KB · Views: 5
Last edited:
Upvote 0
You could use VLOOKUP for this:

Cell Formulas
RangeFormula
G7:K15G7=VLOOKUP(A7,$A$2:$B$5,2,1)&IF(ISODD(A7)," ODD"," EVEN")

It's not clear where the boundaries are? If 10 is LOW LOW, just change cell A3 from 10 to 11.

PS: Sorry this reply is late ... I've been away a couple of weeks.
 
Upvote 0
Solution
Hi Stephen, that's ok, and thank you for replying anyway. I really appreciate it because, while someone else gave me a formula,
=IF(C2>$AF$4,"HIGH HIGH",IF(C2>$AF$3,"LOW HIGH",IF(C2>$AF$2,"HIGH LOW","LOW LOW")))&" " &IF(ISODD(C2),"ODD","EVEN")
this vlookup is so much more robust and shorter.
Thank you so much
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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