Quick Links ▼

Bookmark page

Printable version

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel formula

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Excel formula

Resolved · High Priority · Version 2007

Reply Tue 17 Jan 2012, 08:50Delegate Charles said...

Charles has attended:
Excel Intermediate course

Excel formula

I have an array formula that looks like this:

{=MIN(IF('SHOT REPORT'!B$7:B$540='SCHEDULE TIMELINE'!A89,'SHOT REPORT'!BG$7:BG$540))}

This works really well and returns the minimum value from BG7 to BG540.
I'd like to add another bit to the formula that would say if there's a value in cell BI7 to BG540 of the Shot report to retrun the min value of that cell.

Please help!

Thanks
Charles

For upcoming training course dates see: Pricing & availability

Reply Tue 24 Jan 2012, 13:15Trainer Doug said...

RE: Excel formula

Hi Charles

I've reporoduced your formula and as you say it works. Can you say a little more about the extra part to add? Or if possible send a copy of the file to enquiries

It would help to include in subject please forward to Doug.

Thanks
Doug

Doug Dunn
Best STL

Reply Tue 24 Jan 2012, 13:42Delegate Charles said...

RE: Excel formula - please forward to Doug

I'd like the extra bit of the formula to say something like "if there's a value in BI7 to BI540 retutn that ...instead of the value in BG7 to BG54"

So if there's nothing in BI7 to BI540 I get the valuse from BG7 to BG54 ...but if there's soemthing in BI7 to BI540 I get that instead.

Thanks!
Charles

Reply Mon 30 Jan 2012, 10:28Trainer Doug said...

RE: Excel formula - please forward to Doug

Hi Charles

This formula returns the min value in B7:B540 if it finds a value or the min value from BG7:BG540 if it doesn't.

=MIN(IF('SCHEDULE TIMLINE'!A89='SHOT REPORT'!$B$7:$B$540,'SHOT REPORT'!$B$7:$B$540,'SHOT REPORT'!$BG$7:$BG$540))

I'm not sure if that's quite what you are asking. To test if there are any values in B7:B540 you could try using the ISNUMBER function. However I can't find a way of incorporating that into the array formula.

Let me know if find a way, thanks.

Doug

Mon 6 Feb 2012: Automatically marked as resolved.

Excel articles

· How Excel Can Boost Even The Smallest Business
· Creating Smarter, Customized Invoices for your Small Business with Microsoft Excel
· Microsoft Excel as a Reporting Tool
· Using The Drawing Toolbar In Excel
· Reasons Why VBA for Excel Training Fails

Please browse our web site to find out more about
advanced excel class and other Microsoft training courses.

Excel tip:

Quickly insert a function

In Excel 97 and 2000 it was known as the Paste Function dialog box, these days it's known as the Insert Function dialog box. Regardless, one has to choose Insert|Function. or the fx button to open it up. There is, however, a non-mousey way to get hold of the Insert Function dialog box: press Shift+F3 in a blank cell to open the Insert Function dialog.

Press Shift+F3 after a function name and open bracket to open the Function Arguments dialog. For example, type =VLOOKUP( into a cell and press Shift+F3 to obtain a detailed description of VLOOKUP's arguments.

View all Excel hints and tips