Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-22-2017, 04:59 AM
am_gup123 am_gup123 is offline Fetch highest number Windows 7 64bit Fetch highest number Office 2016
Novice
Fetch highest number
 
Join Date: Mar 2017
Posts: 3
am_gup123 is on a distinguished road
Default Fetch highest number

Hi,



I need help in writing an excel formulae to achieve this –

In Sheet1, there are 4 columns Date,A,B,C,D. These columns contain different numbers while the date field contains date, the data is sorted in ascending order of date.
In Sheet2, I have a date range (say date1 & date2).

What I want is to fetch the highest number among columns A,B,C,D in sheet1 in the specified date range (date 1 & date2).

Example-
Sheet1 :
Date------A-B-C-D
1-Jan-17-1-2-3-5
2-Jan-17-6-4-5-7
3-Jan-17-1-2-5-3
4-Jan-17-3-4-3-6
5-Jan-17-6-4-1-7
6-Jan-17-2-7-9-4
7-Jan-17-6-1-2-4
8-Jan-17-3-6-9-3
9-Jan-17-1-3-2-1

Sheet2:
Date1=3-jan17 ; date2=7-jan-17
Highest number between this date range is 9…..so 9 should be displayed.

Thanks.
Reply With Quote
  #2  
Old 03-22-2017, 06:41 AM
xor xor is offline Fetch highest number Windows 10 Fetch highest number Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

With your data in A2:E10 and date1 and date2 in G1 and G2 try this array formula:

=MAX(IF((A2:A10>=G1)*(A2:A10<=G2),B2:E10))

Remember to hold down Ctrl and Shift before pressing Enter.
Reply With Quote
  #3  
Old 03-23-2017, 01:56 AM
am_gup123 am_gup123 is offline Fetch highest number Windows 7 64bit Fetch highest number Office 2016
Novice
Fetch highest number
 
Join Date: Mar 2017
Posts: 3
am_gup123 is on a distinguished road
Thumbs up

Excellent !!...that worked....


Thanks a lot for your help.
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
Fetch highest number How to specify the Outlook_account to fetch an email from? Officer_Bierschnitt Excel Programming 2 11-24-2015 04:34 AM
How to fetch data from one sheet to another with complete details and date wise as well harisjawed86 Excel Programming 1 08-05-2014 09:10 PM
Fetch highest number Fetch images from URLs automatically claftchi Word VBA 1 12-25-2013 01:25 AM
Highest & lowest Numbers ibrahimaa Excel 3 04-23-2012 07:52 PM
Linking sheets to fetch transactions from sheets to another waqer Excel 4 09-01-2011 12:35 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:49 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft