Conditional formula to find value before the x records

[Brew]

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

 

[erik.van.geit]

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,

 

[Brew]

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.

 

[erik.van.geit]

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

 

[Brew]

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

 

[erik.van.geit]

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)

 

[Brew]

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

 

[erik.van.geit]

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

 

[Brew]

Thanks for help, this problem is solved!!!

 

[erik.van.geit]

Thanks for help, this problem is solved!!!

You're WELCOME !!!
happy with you
you learned something and will be stronger for the next challenge