| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated If Statement...?
Hello all! I'm perplexed, frustrated, and disturbed I can't figure this one
out. I need to come up with a multilayer "if" statement function that covers the following information for translation and recording fees A B C D E Language Trans Min Rec Min Trans per Word Rec Charges per Word # of words: 871 Afrikaans $85.00 $45.00 $226.46 HELP Arabic $105.00 $55.00 $261.30 HELP Austrian German $75.00 $40.00 $209.04 HELP o.k... if the # of words is <999, then the rec charges per word is C if the # of words is 1000<1999, then the rec charges per word is 2xC if the # of words is 2000<2999, then the rec charges per word is 2.5xC if the # of words is 3000<3999, then the rec charges per word is 3xC So, for Afrikaans. if the number of words is 1342. then column E should populate as $90.00. I greatly appreciate any and all help! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complicated If Statement...?
You don't need any IFs. Consider if the number of words is in H1, then
=ROUNDDOWN(H1/1000,0)+1 will yield 1 if H1 is between 0 and 999 will yield 2 if H1 is between 1000 and 1999 will yield 3 if H1 is between 2000 and 2999 will yield 4 if H1 is between 3000 and 3999 so: =CHOOSE(ROUNDDOWN(H2/1000,0)+1,C1,2*C1,2.5*C1,3*C1) should be what you need -- Gary''s Student gsnu200705 "MeiLong" wrote: Hello all! I'm perplexed, frustrated, and disturbed I can't figure this one out. I need to come up with a multilayer "if" statement function that covers the following information for translation and recording fees A B C D E Language Trans Min Rec Min Trans per Word Rec Charges per Word # of words: 871 Afrikaans $85.00 $45.00 $226.46 HELP Arabic $105.00 $55.00 $261.30 HELP Austrian German $75.00 $40.00 $209.04 HELP o.k... if the # of words is <999, then the rec charges per word is C if the # of words is 1000<1999, then the rec charges per word is 2xC if the # of words is 2000<2999, then the rec charges per word is 2.5xC if the # of words is 3000<3999, then the rec charges per word is 3xC So, for Afrikaans. if the number of words is 1342. then column E should populate as $90.00. I greatly appreciate any and all help! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complicated If Statement...?
How about over 3999?
This formula treats anything over 2999 as 3*C =LOOKUP(D2,{0,999,1999,2999},{1,2,2.5,3})*C2 Entered in E2 and copied down. Gord Dibben MS Excel MVP On Mon, 12 Feb 2007 11:55:00 -0800, MeiLong wrote: Hello all! I'm perplexed, frustrated, and disturbed I can't figure this one out. I need to come up with a multilayer "if" statement function that covers the following information for translation and recording fees A B C D E Language Trans Min Rec Min Trans per Word Rec Charges per Word # of words: 871 Afrikaans $85.00 $45.00 $226.46 HELP Arabic $105.00 $55.00 $261.30 HELP Austrian German $75.00 $40.00 $209.04 HELP o.k... if the # of words is <999, then the rec charges per word is C if the # of words is 1000<1999, then the rec charges per word is 2xC if the # of words is 2000<2999, then the rec charges per word is 2.5xC if the # of words is 3000<3999, then the rec charges per word is 3xC So, for Afrikaans. if the number of words is 1342. then column E should populate as $90.00. I greatly appreciate any and all help! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complicated If Statement...?
You are correct.
But the ROUNDDOWN() isn't the answer. The CHOOSE() is -- Gary's Student gsnu200705 "Sandy Mann" wrote: =ROUNDDOWN(H1/1000,0)+1 will yield 3 if H1 is between 2000 and 2999 The OP wanted 2.5 To do it with IF()'s: =IF(H1=0,0,IF(H1<1000,1,IF(H1<2000,2,IF(H1<3000,2. 5,3))))*C1 or without IF()'s =IF(H1=0,0,((CEILING(H1,1000)/1000+1)/2+(H11000)/2)*C1) -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Gary''s Student" wrote in message ... You don't need any IFs. Consider if the number of words is in H1, then =ROUNDDOWN(H1/1000,0)+1 will yield 1 if H1 is between 0 and 999 will yield 2 if H1 is between 1000 and 1999 will yield 3 if H1 is between 2000 and 2999 will yield 4 if H1 is between 3000 and 3999 so: =CHOOSE(ROUNDDOWN(H2/1000,0)+1,C1,2*C1,2.5*C1,3*C1) should be what you need -- Gary''s Student gsnu200705 "MeiLong" wrote: Hello all! I'm perplexed, frustrated, and disturbed I can't figure this one out. I need to come up with a multilayer "if" statement function that covers the following information for translation and recording fees A B C D E Language Trans Min Rec Min Trans per Word Rec Charges per Word # of words: 871 Afrikaans $85.00 $45.00 $226.46 HELP Arabic $105.00 $55.00 $261.30 HELP Austrian German $75.00 $40.00 $209.04 HELP o.k... if the # of words is <999, then the rec charges per word is C if the # of words is 1000<1999, then the rec charges per word is 2xC if the # of words is 2000<2999, then the rec charges per word is 2.5xC if the # of words is 3000<3999, then the rec charges per word is 3xC So, for Afrikaans. if the number of words is 1342. then column E should populate as $90.00. I greatly appreciate any and all help! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Complicated If statement? | Excel Discussion (Misc queries) | |||
| Something perhaps a little complicated | Excel Discussion (Misc queries) | |||
| Complicated | Excel Worksheet Functions | |||
| Complicated If Then / V Lookup / Match Statement... | Excel Worksheet Functions | |||
| semi-complicated nested IF statement | Excel Worksheet Functions | |||
Linear Mode
