update cell with conditional formatting

[skunkworks]

In the example spreadsheet, is it possible to update column E with a number based on the following criteria?
column D > 10.00 and column B = 0.00 or blank: code 1
column D > 10.00 and column B > 0.00: code 2
column D < 10.00 and column C > 0.00: code 3
column D < 10.00 and column C = 0.00 or blank: code 4

Author	2024 Q3 bal	2024 Q4 royalty	due	code	currency
Author A	7.11	2.01	9.12	3	GBP
Author B	0.00	30.92	30.92	1	USD
Author C	6.18	5.81	11.99	2	USD
Author D	5.18	6.97	12.15	2	USD
Author E	2.18	1.82	4.00	3	USD
Aughor F	5.56		5.56	4	USD
Author G	9.56		9.56	4	USD
Author H	25.38		25.38	4	USD
Author I	0.00	15.41	15.41	1	USD

 

[6StringJazzer]

What you are asking for is unrelated to conditional formatting.

Put in E2, and copy down.

=IF(D2>10,IF(B2>0,2,1),IF(C2>0,3,4))

What do you want the result to be if column C = 10.00?

 

[skunkworks]

Thank you, Jeff! To answer your question, it depends. In order to be paid, an author must have a balance of 10.00 or more (column D). If royalties are accrued in the current quarter (column C) and there are no royalties due from the previous quarter (column B), the code is 1.

If column B and column C add up to more than 10.00 in column D, the code is 2.