Complicated If Statement...?

[MeiLong]

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!

 

[Gary''s Student]

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

 

[Sandy Mann]

=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+(H1>1000)/2)*C1)

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Gord Dibben]

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

 

[Gary''s Student]

You are correct.

But the ROUNDDOWN() isn't the answer. The CHOOSE() is