Question
 | | | | | 
May 7, 2006, 11:06 PM
| | New Member  | | Join Date: Sep 2005 Location: Zagreb, HR
Posts: 9
| | | Excel: 2 criteria - 1 answer
GMorning Ladies/Gentlemen!!
Here's a small question:
is it possible to have two criteria which are filled in by a user, and in a third box an answer appears?! I have put an example attached in zip format.
Many thanks,
Srecak
| | | | | | |
Answers
| | | | | |
May 8, 2006, 01:56 AM
|
#2
| | Administrator
Join Date: Aug 2005 Location: Cave 4, Qumran
Posts: 6,889
| Yes, it is possible. I friend made one for me, attached. I'm sorry to say that I don't know how he did it, but if no one posts exact instructions here, you can look at the cells and see how it works. |
 | | | | | | | | | | | | |
May 8, 2006, 05:09 AM
|
#3
| | | Computer Expert
Join Date: Jan 2003 Location: LI, NY - USA
Posts: 21,904
Pay to call ScottGem for advice ($.75/min) | The sheet Rick attached basically use formuals to make the clacs.
The way Excel works, if you can create calculations that use entries in other cells, just by referencing the cell location: I.E. = A1+B1
Put that formula in C1 and it will display the sum of A1 and B1.
If you need a further explanation plese post more details. | |
| | | | | | | | | | | | |
May 8, 2006, 05:21 AM
|
#4
| | New Member
Join Date: Sep 2005 Location: Zagreb, HR
Posts: 9
| Sorry Guys, but thats not quiet what I want... not calculations but retrieval, based on two criteria... weight (between 0.5 - 3.0 kg) and zone (1 to 8). The centre parts top line shows the basis price and each different weight an additional cost to this 1st price.
So if in criteria 1, I put 3.7kg and criteria 2, I put 4 as a zone, I should get a retrieval value/price of: 3.7kg goes under 3.0-5.0kg and zone 4 is 10.00 euros, plus the basic price for zone 4 being 55.00 euros adds up to 65.00 euros!!
Hope this makes it easier to understand?!
Srecak | |
| | | | | | | | | | | | |
May 8, 2006, 05:25 AM
|
#5
| | Administrator
Join Date: Aug 2005 Location: Cave 4, Qumran
Posts: 6,889
| Sorry. Yes, I did misunderstand. Hang tight, there are several Excel gurus hanging out here.  | |
| | | | | | | | | | | | |
May 8, 2006, 05:44 AM
|
#6
| | | Computer Expert
Join Date: Jan 2003 Location: LI, NY - USA
Posts: 21,904
Pay to call ScottGem for advice ($.75/min) | Look up Microsoft Query in Excel Help. You can use that to perform such queries. | |
| | | | | | | | | | | | |
May 8, 2006, 06:05 AM
|
#7
| | Ultra Member
Join Date: Feb 2004 Location: Cambridge, UK
Posts: 1,047
| VLookup & HLookup might be handy here. If you don't have an example when I get home - I'll write one for you. | |
| | | | | | | | | | | | |
May 16, 2006, 09:05 AM
|
#8
| | New Member
Join Date: Sep 2005 Location: Zagreb, HR
Posts: 9
| LTheobald,
Sorry for the late reply!
An example would be great!!
Many thanks
Srecak | |
| | | | | | | | | | | | |
May 16, 2006, 03:28 PM
|
#9
| | Full Member
Join Date: Jan 2006 Location: Boston, MA - USA
Posts: 426
| I couldn't figure out how to do it using HLOOKUP and VLOOKUP (doesn't mean it can't be done) but I came up with a different way using the functions INDEX and COUNTIF. I did have to make a modification to the range where you lookup the values as I don't believe EXEL has a way of interpreting the values in the cells (at least not easily). I basically broke the values into two columns. Your table also lists ranges with gaps in them. i.e. 2.6kg doesn't exactly fall between 0.5-2.5 or 3.0-5.0 so I broke out that range and made the assumption that if the value is less <3.0 but greater than 0.5 then it is assumed that it is in the range of 0.5-2.5 so anything less than 3.0 kg but greater or equal to 0.5 will get the rate of 0.5-2.5. Confusing but take a look at the attached.
Casey | |
| | | | | | | | | | | | |
May 17, 2006, 11:20 PM
|
#10
| | New Member
Join Date: Sep 2005 Location: Zagreb, HR
Posts: 9
| Cajalat!!
You have saved my bacon!! This works brilliantly!
Many thanks,
Srecak | |
| | | | | | | | Question Tools | Search this Question | | | | | Display Modes |  Linear Mode | |
