# Conditional formula to find value before the x records

#### Brew

##### Well-known Member
How do you create a conditional formula for the value in the cell that is immediately before the cells with the "x" record in the range U4:U1003

Also, a conditional formula for the highest value in the range U4:U1003

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Hi,

1t question is unclear to me
an example would help

2nd
1. check out the helpfiles for MAX
2. when using it as a CF-formula (I suppose you want it for the enitre range, think about using absolute cellreferences, else it won't work

kind regards,
Erik,

Here are examples:
1st condition
u211=7 and u210=6, u210 does not meet 1st condition
u212=x and u211=7, therefore, u211 met 1st condition
u231=x and u230=16, therefore, u230 met 1st condition
u232=x and u231=x, u231 does not meet 1st condition
u233=1 and u232=x, u232 does not meet 1st condition
u234=2 and u233=1, u233 does not meet 1st condition
2nd condition
u230=16 and u211=7, then u230 meets 2nd condition,
because it is the highest value (16) in the range.

the value in the cell that is immediately before the cells with the "x" record
with your examples you made clear what you meant with "before", that's the information which was needed
BUT this example
u232=x and u231=x, u231 does not meet 1st condition
should be: u231 met 1st condition
else you didn't express correctly the condition in your first post
can you clarify this ?
TIP: it would be fine for potential responders to see examples in a table: this is much easier to read

u231 does not meet the condition, because although u232 was= to x, u231 is not a numeric value, rather text, so the condition is only valid if the an x record follows a numeric value, then that numeric value meet that condition

to check for a number, you need ISNUMBER
to check if something is equal to a given string, you need A1 = "string"
to check if BOTH are TRUE you need =AND(condition1,condition2)

select A2
formula CF
=AND(ISNUMBER(A2),A1="x")
(here you will need relative rowreferences)

erik, this is a little confusing, however the solution works when I modified it to the following: =AND(ISNUMBER(U4),U5="x") and copied for the entire range u4:u1003

Now, How do I find the highest numeric value in the range U4:U1003, using CF

this is a little confusing, however the solution works when I modified it to the following: =AND(ISNUMBER(U4),U5="x") and copied for the entire range u4:u1003
as you have over 1000 posts, we can consider you would know how to browse this site (search), how to use the helpfiles + how to experiment with basic formulas to get the results you need
we can provide you with totally "ready for use" examples or we can choose to learn how to fish
a CF-formula can be tested as a normal formula
example: CF to check if a cell has same value as sum of two others
in B1 you can write the formula: =A1=A2+A3
changing values in A1,A2,A3 you will see the results TRUE or FALSE
if this is OK, then you select A1 /CF / formula: =A2+A3

How do I find the highest numeric value in the range U4:U1003, using CF
1. check out the helpfiles for MAX
2. when using it as a CF-formula (I suppose you want it for the enitre range, think about using absolute cellreferences, else it won't work

Thanks for help, this problem is solved!!!

Thanks for help, this problem is solved!!!
You're WELCOME !!!
happy with you
you learned something and will be stronger for the next challenge

Replies
2
Views
314
Replies
1
Views
106
Replies
5
Views
212
Replies
16
Views
2K
Replies
1
Views
196

1,221,314
Messages
6,159,187
Members
451,544
Latest member
MrsGrayMarlin

### 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.

### Which adblocker are you using?

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

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