| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IFs and ORs
Tried every combination I could think of, here's what I need for for a
solution for ONE cell: If D1=2 and D3 is <14 then D5=0 , If D1=2 and D3 is 14 then D3-14*F1 OR If D1=3 and D3 is <16 then D5=0 , If D1=3 and D3 is 16 then D3-16*F1 OR If D1=4 and D3 is <17 then D5=0 , If D1=4 and D3 is 17 then D3-17*F1 How can I get this into a single formula for an answer in one cell? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IFs and ORs
Try in D5
=IF(AND(D1=2,D3<14),0,IF(AND(D1=2,D3=14),D3-14*F1,IF(AND(D1=3,D3<16),0,IF(AND(D1=3,D3=16),D3-16*F1,IF(AND(D1=4,D3<17),0,IF(AND(D1=4,D3=17),D3-17*F1,"ERROR")))))) I am testing it ... You do too. "DavidT" wrote: Tried every combination I could think of, here's what I need for for a solution for ONE cell: If D1=2 and D3 is <14 then D5=0 , If D1=2 and D3 is 14 then D3-14*F1 OR If D1=3 and D3 is <16 then D5=0 , If D1=3 and D3 is 16 then D3-16*F1 OR If D1=4 and D3 is <17 then D5=0 , If D1=4 and D3 is 17 then D3-17*F1 How can I get this into a single formula for an answer in one cell? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IFs and ORs
=IF(AND(D1=2,D3<14),0,IF(AND(D1=2,D3=14),D3-14*F1,IF(AND(D1=3,D3<16),0,IF(AND(D1=3,D3=16),D3-16*F1,IF(AND(D1=4,D3<17),0,IF(AND(D1=4,D3=17),D3-17*F1,"ERROR"))))))
Seems to be Ok. Note that I have added equality condition also... "DavidT" wrote: Tried every combination I could think of, here's what I need for for a solution for ONE cell: If D1=2 and D3 is <14 then D5=0 , If D1=2 and D3 is 14 then D3-14*F1 OR If D1=3 and D3 is <16 then D5=0 , If D1=3 and D3 is 16 then D3-16*F1 OR If D1=4 and D3 is <17 then D5=0 , If D1=4 and D3 is 17 then D3-17*F1 How can I get this into a single formula for an answer in one cell? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IFs and ORs
On Sat, 25 Oct 2008 20:23:01 -0700, DavidT
wrote: Tried every combination I could think of, here's what I need for for a solution for ONE cell: If D1=2 and D3 is <14 then D5=0 , If D1=2 and D3 is 14 then D3-14*F1 OR If D1=3 and D3 is <16 then D5=0 , If D1=3 and D3 is 16 then D3-16*F1 OR If D1=4 and D3 is <17 then D5=0 , If D1=4 and D3 is 17 then D3-17*F1 How can I get this into a single formula for an answer in one cell? You don't define what you want as a result should D1 not equal 2, 3 or 4; nor do you define what you want as a result should D3 equal 14, 16, or 17. Assuming in the first case that you will want to return a zero, then either: =SUMPRODUCT((D1={2,3,4})*(D3={14,16,17})*(D3-{14,16,17}*F1)) or =SUMPRODUCT((D1={2,3,4})*(D3{14,16,17})*(D3-{14,16,17}*F1)) depending on what you want to happen if D3 is exactly 14,16, or 17 might work for you. --ron |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IFs and ORs
Your solution worked great once I cleared out "ERROR". What I forgot to ask
is now if there is no data for the solution, I get a return of "FALSE" but would prefer to have an empty cell indicated. I've tried to incorporate "" into D5, but can't get to work. "Sheeloo" wrote: =IF(AND(D1=2,D3<14),0,IF(AND(D1=2,D3=14),D3-14*F1,IF(AND(D1=3,D3<16),0,IF(AND(D1=3,D3=16),D3-16*F1,IF(AND(D1=4,D3<17),0,IF(AND(D1=4,D3=17),D3-17*F1,"ERROR")))))) Seems to be Ok. Note that I have added equality condition also... "DavidT" wrote: Tried every combination I could think of, here's what I need for for a solution for ONE cell: If D1=2 and D3 is <14 then D5=0 , If D1=2 and D3 is 14 then D3-14*F1 OR If D1=3 and D3 is <16 then D5=0 , If D1=3 and D3 is 16 then D3-16*F1 OR If D1=4 and D3 is <17 then D5=0 , If D1=4 and D3 is 17 then D3-17*F1 How can I get this into a single formula for an answer in one cell? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IFs and ORs
Ron: If D1 is left blank I then wanted a return of "", sorry I didn't think
of it! Your solution gave me a hugh negative answer when there was data, and "#VALUE" if there was no data. "Ron Rosenfeld" wrote: On Sat, 25 Oct 2008 20:23:01 -0700, DavidT wrote: Tried every combination I could think of, here's what I need for for a solution for ONE cell: If D1=2 and D3 is <14 then D5=0 , If D1=2 and D3 is 14 then D3-14*F1 OR If D1=3 and D3 is <16 then D5=0 , If D1=3 and D3 is 16 then D3-16*F1 OR If D1=4 and D3 is <17 then D5=0 , If D1=4 and D3 is 17 then D3-17*F1 How can I get this into a single formula for an answer in one cell? You don't define what you want as a result should D1 not equal 2, 3 or 4; nor do you define what you want as a result should D3 equal 14, 16, or 17. Assuming in the first case that you will want to return a zero, then either: =SUMPRODUCT((D1={2,3,4})*(D3={14,16,17})*(D3-{14,16,17}*F1)) or =SUMPRODUCT((D1={2,3,4})*(D3{14,16,17})*(D3-{14,16,17}*F1)) depending on what you want to happen if D3 is exactly 14,16, or 17 might work for you. --ron |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IFs and ORs
Replace "ERROR" by "" to get a blank if none of the conditions are met...
"DavidT" wrote: Your solution worked great once I cleared out "ERROR". What I forgot to ask is now if there is no data for the solution, I get a return of "FALSE" but would prefer to have an empty cell indicated. I've tried to incorporate "" into D5, but can't get to work. "Sheeloo" wrote: =IF(AND(D1=2,D3<14),0,IF(AND(D1=2,D3=14),D3-14*F1,IF(AND(D1=3,D3<16),0,IF(AND(D1=3,D3=16),D3-16*F1,IF(AND(D1=4,D3<17),0,IF(AND(D1=4,D3=17),D3-17*F1,"ERROR")))))) Seems to be Ok. Note that I have added equality condition also... "DavidT" wrote: Tried every combination I could think of, here's what I need for for a solution for ONE cell: If D1=2 and D3 is <14 then D5=0 , If D1=2 and D3 is 14 then D3-14*F1 OR If D1=3 and D3 is <16 then D5=0 , If D1=3 and D3 is 16 then D3-16*F1 OR If D1=4 and D3 is <17 then D5=0 , If D1=4 and D3 is 17 then D3-17*F1 How can I get this into a single formula for an answer in one cell? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IFs and ORs
Sheeloo: Thanks a BUNCH!
"Sheeloo" wrote: Replace "ERROR" by "" to get a blank if none of the conditions are met... "DavidT" wrote: Your solution worked great once I cleared out "ERROR". What I forgot to ask is now if there is no data for the solution, I get a return of "FALSE" but would prefer to have an empty cell indicated. I've tried to incorporate "" into D5, but can't get to work. "Sheeloo" wrote: =IF(AND(D1=2,D3<14),0,IF(AND(D1=2,D3=14),D3-14*F1,IF(AND(D1=3,D3<16),0,IF(AND(D1=3,D3=16),D3-16*F1,IF(AND(D1=4,D3<17),0,IF(AND(D1=4,D3=17),D3-17*F1,"ERROR")))))) Seems to be Ok. Note that I have added equality condition also... "DavidT" wrote: Tried every combination I could think of, here's what I need for for a solution for ONE cell: If D1=2 and D3 is <14 then D5=0 , If D1=2 and D3 is 14 then D3-14*F1 OR If D1=3 and D3 is <16 then D5=0 , If D1=3 and D3 is 16 then D3-16*F1 OR If D1=4 and D3 is <17 then D5=0 , If D1=4 and D3 is 17 then D3-17*F1 How can I get this into a single formula for an answer in one cell? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IFs and ORs
On Sun, 26 Oct 2008 09:00:16 -0700, DavidT
wrote: Ron: If D1 is left blank I then wanted a return of "", sorry I didn't think of it! Your solution gave me a hugh negative answer when there was data, and "#VALUE" if there was no data. What if D1 is not = to 2,3 or 4? The result being a negative number is solely a function of the values you have in D1, D3 and F1 For example, if D1=3 D3=18 F1 = 4 Then If D1=3 and D3 is <16 then D5=0 , If D1=3 and D3 is 16 then D3-16*F1 D3-16*F1 = 18 - 16*4 = 18 - 64 = -46 The return of a VALUE error means that you have text in D1 or one of the other cells. You can eliminate those kinds of errors by wrapping the formula in an IF statement. e.g. =IF(AND(COUNT(D1,D3,F1)=3, OR(D1={2,3,4})), SUMPRODUCT((D1={2,3,4})*(D3={14,16,17})*(D3-{14,16,17}*F1)),"") --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
