Conditional formula to find value before the x records

Brew

Well-known Member
Joined
Sep 29, 2003
Messages
1,569
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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Well-known Member
Joined
Sep 29, 2003
Messages
1,569
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Well-known Member
Joined
Sep 29, 2003
Messages
1,569

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Well-known Member
Joined
Sep 29, 2003
Messages
1,569

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

Forum statistics

Threads
1,141,757
Messages
5,708,349
Members
421,566
Latest member
7Nabisco

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
Top