You are here:

Excel/4 Values from a range

Advertisement


Question
My input range is AC38:BH38 which contains either a value or is empty. There is also a range, $AC$58:$BH$58, which contains a value of "Y" or is also empty.

If a cell within the range $AC$58:$BH$58 contains the value "Y", then the corresponding cell within row 38 is not empty. I want the value within that cell only if its value is greater than 0.

The answers required are (formatted as Answer#_ = ):

Value of the 'Last cell' (Right hand side) of the input range AC38:BH38 meeting the above 2 conditions.
Value of the 1 cell left of the cell that satisfies Answer #1 if it also satisfies the initial conditions.
Value of the 2 cell LEFT to the above cell(Answer #1) provided it also meets the above conditions.
Value of the 3 cell LEFT to the above cell(Answer #1) provided it also meets the above conditions.

I want 4 answers from the entire range of AC38:BH38 in output cells, say, CA38, CB38, CC38 & CD38. The formula should check all the cells of AC38:BH38 to ascertain which cell/(s) qualifies (a cell qualifies if it meets the above 2 conditions) & then give the answers as per the 4 types of requirements. 2 conditions: Value of the cell (in AC38:BH38) MUST BE>0; the corresponding cell in $AC$58:$BH$58 MUST CONTAIN the value "Y". Ex: AC38=777 & AC58=Y QUALIFIES; AD38=999 & AD58="" DOES NOT QUALIFIES

Answer
Kumar

Sorry, I did not see your question as I am haveing internet problems.
I will not have a connection for long so I will make a quick suggestion.
without trying to put all of your conditions in a single formula, Break down each of your conditions into a formula and put it into a single cell, then use another formula (or more if necesary) to analyze the results of the first formulas and bring back the final results.  This will make the formula writing much less complicated.  Give this a try and I think you will be able to work through the issues.

Sorry I couldn't give you a more detailed answer at this time, but I am limited in internet use.

Richard
Florida
USA  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Richard Roberts

Expertise

Can assist you in most areas of Excel, have been working with it for about 15 years in many types of applications, but primarily in financial and accounting applications. I am a CPA and many client or client problems have necessitated the use of excel. I am not an expert in charting, macros, or pivot tables.

Experience

Have been working with Excel for about 20 years primarily in accounting and financial areas.

Education/Credentials
BA, Certified Public Accountant

©2016 About.com. All rights reserved.