Home |
Search |
Today's Posts |
#1
|
|||
|
|||
calculation based on meeting two criteria
I have the following formula and it returns the wrong calculation
K1 =.03 K2 =.02 In this situation, its supposed to multiply F82*K2, instead it is multiplying F82* K1 =IF(E82="service a",F82*$K$1,IF(AND(E82="Service B",MATCH(C82,'Sheet 2!$C$2:$C$15)),F82*$K$1,F82*$K$2)) any help is appreciated. |
#2
|
|||
|
|||
To simplify your formula try this: =IF(OR(E82="service a",AND(E82="Service B",MATCH(C82,'Sheet 2!$C$2:$C$15))),F82*$K$1,F82*$K$2) -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=473965 |
#3
|
|||
|
|||
MATCH(C82,Sheet2!$C$2:$C$15)
You have the match function set up to find an approximate match, not an exact match. Is this what you intended? If you intend to look for an exact match, this part of your formula would be problematic. Check help for the match function and its argument (the third argument, which you've omitted, specifies the match type. 0 specifies an exact match). If you do intend to look for an exact match, you will probably need to nest MATCH inside NOT(ISERROR(MATCH(....))) or NOT(ISNA(MATCH(.....))) to get the results you want. "jerry" wrote: I have the following formula and it returns the wrong calculation K1 =.03 K2 =.02 In this situation, its supposed to multiply F82*K2, instead it is multiplying F82* K1 =IF(E82="service a",F82*$K$1,IF(AND(E82="Service B",MATCH(C82,'Sheet 2!$C$2:$C$15)),F82*$K$1,F82*$K$2)) any help is appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is the IRR calculation based on cash flows at beginning of year? | Excel Worksheet Functions | |||
Counting the number of cells meeting conditional formating criteria | Excel Worksheet Functions | |||
Calculation based on a condition | Excel Worksheet Functions | |||
counting cells (COUNTIF) based on two or more criteria | Excel Worksheet Functions | |||
Counting an Array based on a calculation | Excel Discussion (Misc queries) |