Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 23-10-2009
Member
 
Join Date: Sep 2009
Posts: 125
Search formula in Excel

I have a table, a column M with a formula "IF". This formula tells me different values ranging from 4 to 0, so decreasing. That is, I have several lines that follow each with 4, then several other lines with 3 .... until 0.

What I would do:
I get to the first cell that displays a 0, the value of cell in the same row, column E.

For example, if the first 0 appears in cell M9, I get the value in cell E9.

I tried with different formulas "Search" I do not really control, could you tell me what formula to return?
Reply With Quote
  #2  
Old 23-10-2009
Member
 
Join Date: Nov 2005
Posts: 1,323
Re: Search formula in Excel

Here is one method (there are probably others), I hope it matches your request:

A set M2 (I imagine you have a title)

Code:
=IF(M1=0;IF(YourFormula);IF(M1=E1;IF(YourFormula);IF(IF(YourFormula)=0;E2;IF(YourFormula))))
Replaces YourFormula by the formula that you have currently in M2 and pulls down.
Reply With Quote
  #3  
Old 23-10-2009
Member
 
Join Date: Sep 2009
Posts: 125
Re: Search formula in Excel

Thank you for your quick response!

I do not think the formula is appropriate. Or maybe it's me who did not understand (which is quite possible too!)

Columns that interest me are the columns M and E. In column M, the values (using a formula IF) range from 2 to 0:
M3 to M18 2
From M19 to M36: 1
From M37: 0

I bring up my cell B7 the value of the cell E when the first 0 in column M.

In my case, it would be to appear in cell B7 the value 37 which corresponds to cell E39.

The interest is to have the user directly to the value 37 under the eyes, without his having to enter formulas or manipulated to do with the column M.

I hope I have been more clear.
Reply With Quote
  #4  
Old 23-10-2009
Member
 
Join Date: Nov 2005
Posts: 1,323
Re: Search formula in Excel

Your explanations was clearer. You can content yourself with counting the number of rows in column M whose value is greater than 0 (1 to get the expected result). Specifically a "sum if" in the matrix and you're done (you can also do this with SUMPRODUCT but I am less familiar).

Code:
{=SUM(IF(M$3:M$47>0;1;0))+1}
To recall who told array formula () for having validated the formula with Ctrl + Shift + Enter
Reply With Quote
  #5  
Old 24-10-2009
henrythomas
 
Posts: n/a
Re: Search formula in Excel

To quickly show all formulas in your Excel spreadsheet hold down the CONTROL key on the keyboard and press the grave accent key.Show formulas does not change the spreadsheet, only the way it is displayed.
Advantage of this formula:
1) It allows you to quickly read through all formulas to check for errors
2) When you click on a formula, Excel outlines in color the cells referenced in the formula. This helps you to trace the data being used in a formula.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Search formula in Excel"
Thread Thread Starter Forum Replies Last Post
Need formula help for MS Excel RlyPlyPanda MS Office Support 1 08-05-2012 01:46 PM
Excel Formula Scott2580 MS Office Support 2 15-02-2011 02:21 PM
Excel formula Me Me Taylor Windows Software 2 06-04-2009 04:11 PM
Need help converting formula into value in Excel prakashseth Software Development 2 20-11-2008 05:15 PM
Need help in excel formula Landon Software Development 2 27-10-2008 03:28 PM


All times are GMT +5.5. The time now is 12:05 AM.