conditional format issue

[Russk68]

Hi All
I am using Excel for Mac 15.4.1

I have a column with a lot of conditional formatting. If I type a 3 digit number in that column and hit enter, only 1 digit is entered because of lag. I have to pause between digits for it to enter properly. If I delete all the conditional formatting and enter a number, it keeps up with me.

I could understand a lag time after I enter a number but I don't understand the lag while typing in a cell.

Can anyone explain this or a fix?

Thank you!

 

[etaf]

its doing a calculation each time you enter something into the cell

Office 15 for Mac is the Office 2016 version
https://en.wikipedia.org/wiki/Microsoft_Office_2016
Windows would be 2013 from What I can see

How old is the Mac

how many conditional formats do you have ?

 

[Russk68]

That is disappointing. It pretty much makes Conditional Formatting useless in my situation.
It's a new Macbook.
I tried 16.10 and it's unusable to me. I had to go back to to 15.

5 rules in 2200 cells

Thank you!

 

[etaf]

???
NOT an expert in these versions - sorry
also on a mac the 32-64 bit may change versions as well

what is 16.10 - office ?? in a MAC
is this the subscription 365 version or the office 2016 standalone version
a little confused on the versions between windows and Mac
on windows office15 = 2013 version and office16 = 2016
365 = V18

can you talk office 2016 or 2013 or 2011 - as the App should be named in the app folder
5 rules in 2200 cells should not be an issue - i have a RGB status in more rows then that

what are the rules - ?
complicated formula

 

[arthurbr]

Just an idea but does your CF use volatile functions?
Also perhaps switch calculations to manual?

 

[Russk68]

16.10 is the latest release for Mac and I do have the 365 subscription.

I believe Office:
2011=14
2013=15
2016=16

This is one of the rules that I suspect is causing lag. It's very cool and was written by a member.
=(A1<>"")*AGGREGATE(14,6,IFERROR(FIND("/"&A1&"/","/"&$B$1:$B$10&"/"),0)+IFERROR((A1-LEFT(B$1:B$10,FIND(">",B$1:B$10)-1)>=0),0)*IFERROR((A1-REPLACE(B$1:B$10,1,FIND(">",B$1:B$10),"")<=0),0),1)

 

[Russk68]

I just became aware of volatile functions and don't know what that means. I will Google it.
My workbook is set to manual when it opens with calculations triggered by macros.

 

[etaf]

so have you tried removing that rule and see what happens

 

[Russk68]

Removed it and it's faster. I just don't understand why CF processes after every key stroke.

 

[etaf]

it says that as a different number/letter and so tests
NOT sure how to simplify that formula
or perhaps a helper column may help

put the formula into a separate column and copy down
then conditional formats can just test for 0 or 1