Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-05-2022, 06:30 AM
DelH DelH is offline Help with formulae please. Windows 10 Help with formulae please. Office 2016
Novice
Help with formulae please.
 
Join Date: May 2022
Posts: 4
DelH is on a distinguished road
Default Help with formulae please.

Hi

I am a novice MS Excel 2016 on Windows 10 and am having trouble with a formulae.

H4 is where I want the result to be.

I have 3x tables (TYPE_AE, TYPE_BCFG and TYPE_DH).

I want Cell H3 to check the distance in cell B3 (450-549), then check the type in cell D3 (A, B, C, D etc). then decide which of 3x tables to look at (tables TYPE_AE, TYPE_BCFG or TYPE_DH) then use the value from G3 (109.09) to find it's nearest value in the relevant table, then display in cell H3 what the plumb value is above the G3 value of 109) in the respective table (eg. 8 Plums).

but how do i put this into formulae?
Please help.

Thanks.


B C D E F G H J K
2 DIST TYPE
3 550-A 1.2 1.1 1.09 109.09 #N/A
4
5
6
7
8
9
10 TYPES A & E
11 2 Plums 3 Plums 4 Plums 5 Plums 6 Plums 7 Plums 8 Plums 9 Plums 10 Plums 11 Plums


12 200-249 89 92 94 95 96 97 98 98 99 99 105
13 250-349 81 87 90 92 93 95 96 97 98 100 109
14 350-449 73 81 86 88 90 93 94 96 97 100 109
15 450-549 63 74 80 84 87 90 92 95 96 100 109
16
17
18 TYPES B, C, F & G
19 2 Plums 3 Plums 4 Plums 5 Plums 6 Plums 7 Plums 8 Plums 9 Plums 10 Plums 11 Plums
20 200-249 89 92 94 95 96 97 98 98 99 99
21 250-349 81 87 90 92 93 95 96 97 98 100
22 350-449 73 81 86 88 90 93 94 96 97 100
23 450-549 63 74 80 84 87 90 92 95 96 100
24
25
26 TYPES D & H
27 2 Plums 3 Plums 4 Plums 5 Plums 6 Plums 7 Plums 8 Plums 9 Plums 10 Plums 11 Plums
28 200-249 89 92 94 95 96 97 98 98 99 99
29 250-349 81 87 90 92 93 95 96 97 98 100
30 350-449 73 81 86 88 90 93 94 96 97 100
31 450-549 63 74 80 84 87 90 92 95 96 100
Reply With Quote
  #2  
Old 05-05-2022, 12:07 PM
Steve Kunkel Steve Kunkel is offline Help with formulae please. Windows 10 Help with formulae please. Office 2019
Advanced Beginner
 
Join Date: May 2019
Location: Seattle area
Posts: 67
Steve Kunkel is on a distinguished road
Default

If possible, attach the actual sheet, so we can better see what the values are in the cells. For example, does C12 have "200-249" or is that in two different cells?
Reply With Quote
  #3  
Old 05-05-2022, 01:02 PM
DelH DelH is offline Help with formulae please. Windows 10 Help with formulae please. Office 2016
Novice
Help with formulae please.
 
Join Date: May 2022
Posts: 4
DelH is on a distinguished road
Default

Hi Steve,
Thanks for your reply.
Please see below.
Hope this helps.
I am working tomorrow so will get back to you as soon as I can.
Attached Files
File Type: docx EBI200 TEST.docx (36.5 KB, 6 views)
Reply With Quote
  #4  
Old 05-05-2022, 01:42 PM
ArviLaanemets ArviLaanemets is offline Help with formulae please. Windows 8 Help with formulae please. Office 2016
Expert
 
Join Date: May 2017
Posts: 703
ArviLaanemets is a name known to allArviLaanemets is a name known to allArviLaanemets is a name known to allArviLaanemets is a name known to allArviLaanemets is a name known to allArviLaanemets is a name known to all
Default

My advice is use a single table instead of 3 tables, like:
MinDistance, MaxDistance, Type, Plums, MinValue, MaxValue
200 250 A 2 89 92
200 250 A 3 92 94
...
450 550 E 11 100 110
...
450 550 H 11 100 110

Now your calculation will be a simple SUMIFS() formula.
Reply With Quote
  #5  
Old 05-05-2022, 01:44 PM
Steve Kunkel Steve Kunkel is offline Help with formulae please. Windows 10 Help with formulae please. Office 2019
Advanced Beginner
 
Join Date: May 2019
Location: Seattle area
Posts: 67
Steve Kunkel is on a distinguished road
Default

Still not sure I understand (I'm just a noob, by the way), but...

You mentioned "check the distance in cell B3 (450-549)." Will that value always be exactly the same (i.e. matching strings) as one of the values in the NORMAL POWER tables?
Reply With Quote
  #6  
Old 05-05-2022, 11:25 PM
ArviLaanemets ArviLaanemets is offline Help with formulae please. Windows 8 Help with formulae please. Office 2016
Expert
 
Join Date: May 2017
Posts: 703
ArviLaanemets is a name known to allArviLaanemets is a name known to allArviLaanemets is a name known to allArviLaanemets is a name known to allArviLaanemets is a name known to allArviLaanemets is a name known to all
Default

Here is an example what I thought in my yesterday's post.

Enter data from all your 3 normal power tables into table on NormalPower sheet (i filled it only enough to use your example data) - for all frequencies from A through D.

On sheet EBI200, edit the value returned when data were out of range, i.e. the formula returns 0. Currently the empty string is returned when this is the case.
Attached Files
File Type: xlsx AnExample.xlsx (14.1 KB, 4 views)
Reply With Quote
  #7  
Old 05-06-2022, 12:16 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Help with formulae please. Windows 7 64bit Help with formulae please. Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,578
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

Quote:
Originally Posted by DelH View Post
Hi Steve,
Thanks for your reply.
Please see below.
Hope this helps.
I am working tomorrow so will get back to you as soon as I can.
Why post a Word document if you have an Excel problem?
It would be nice we did not have to retype your data, and, no, I did not even open the Word document
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #8  
Old 05-07-2022, 06:37 AM
Steve Kunkel Steve Kunkel is offline Help with formulae please. Windows 10 Help with formulae please. Office 2019
Advanced Beginner
 
Join Date: May 2019
Location: Seattle area
Posts: 67
Steve Kunkel is on a distinguished road
Default

I think he may have been responding to my inquiry about the content of a particular column. The Word doc (which contains a screenshot) did help a little. ...though yes, I was expecting an xls file.

Anyway... Arvi's recommendation is the way to go. If at all possible, convert your tables into a more "Excel-friendly" format.
Reply With Quote
  #9  
Old 05-11-2022, 12:26 PM
DelH DelH is offline Help with formulae please. Windows 10 Help with formulae please. Office 2016
Novice
Help with formulae please.
 
Join Date: May 2022
Posts: 4
DelH is on a distinguished road
Default

Thanks Avril
Reply With Quote
  #10  
Old 05-17-2022, 02:42 AM
DelH DelH is offline Help with formulae please. Windows 10 Help with formulae please. Office 2016
Novice
Help with formulae please.
 
Join Date: May 2022
Posts: 4
DelH is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
My advice is use a single table instead of 3 tables, like:
MinDistance, MaxDistance, Type, Plums, MinValue, MaxValue
200 250 A 2 89 92
200 250 A 3 92 94
...
450 550 E 11 100 110
...
450 550 H 11 100 110

Now your calculation will be a simple SUMIFS() formula.
Thanks to both Avril and Steve, but I have been sent a formulae which involves minimal changes and uses VLOOKUP, INDEX and MAPPING. Thanks anyway.
Reply With Quote
Reply

Tags
index and match, lookup, vlookup

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with formulae please. Keep formulae pointed to same cell Mechaman Excel 2 04-11-2021 02:54 AM
Excel thinks I am creating a formulae TimTDP Excel 2 07-28-2020 12:30 PM
What's wrong with formulae? kingston123 Excel 5 06-29-2020 10:55 AM
help with formulae Zoemoe Excel 3 07-02-2017 05:40 PM
Help with formulae please. Use NUMPAGES in formulae Friedebarth Word 1 11-30-2011 02:22 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:15 AM.


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