#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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?
|
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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? |
#6
|
|||
|
|||
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. |
#7
|
||||
|
||||
Quote:
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 |
#8
|
|||
|
|||
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. |
#9
|
|||
|
|||
Thanks Avril
|
#10
|
|||
|
|||
Quote:
|
Tags |
index and match, lookup, vlookup |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
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 |
Use NUMPAGES in formulae | Friedebarth | Word | 1 | 11-30-2011 02:22 PM |