More than 1 either or calulations

[Jim]

Hello, I'm a bit stuck as I have to find a formula to apply more than one
rule into the smae formula that i am unsure of a would appreaciate some
assistance, my prolem is as follows:
I have a table of information
1 A B
2 Date Weight
3 October 301
4 October 120
5 December 156
6 October 410
7 December 522

With this I need to find a formula that will apply the following rules for
October only:

-If the weight in column B is below 167 then a flat rate per line of 25 is
applied but if the weight is over 167 then the weight is to be multipied by
0.15

then that is to be added to the rule (using the same data)

-If the weight in column B is below 400 then a flat rate per line of 20 is
applied but if the weight is over 400 then the weight is to be multipied by
0.05

plus

- the number of 'Octobers' * 14

So for each caluation there would be 2 either or calulations that would be
added togetherand then added to a (flat number figure, number of Octobers,
mulitpied by 14) all from the listed table. I am not sure how to combine a
countif formula with one that would calulate the either or number that i am
looking for.

Can any one help?

 

[Pete_UK]

I'm a bit confused. Your first October weight is 301, which is more
than 165 so would have a factor of 0.15 applied to it (i.e. 45.15).
However, it is also less than 400, so it should be taken as a flat
value of 20.

So, what is it to be?

Pete

 

[Jim]

Pete, thanks for looking at my question.
It is both the formula should be able use both to give, for the weight 301
it would be (301*0.15 as is above 167)+(flat 20 as is over 400)+(1*14 as it
is one 'october')

but for
October 120 would be ( 1*25 as it is below 167)+(1*20 as is below
400)+(1*14 as it is one 'october')

and

October 410 would be (410*0.15 as it is over 167)+(410*0.05 as it is over
400)+(1*14 as it is one 'october')

Does that clarify it any better? Sorry i haven't explained myself very well
as i am not too good at translating the information in to Excel lingo. Hope
this helps clarify?

 

[Pete_UK]

Okay, Jim, try this array* formula:

=SUM(IF(A3:A7="October",IF(B3:B7<167,25,0.15*B3:B7)+IF
(B3:B7<400,20,0.05*B3:B7)+14,0))

You may need to change the ranges to suit your real data.

*An array formula needs to be committed using the key combination of
Ctrl-Shift-Enter (CSE) rather than the usual <Enter>. If you do this
correctly then Excel will add curly braces { } around the formula when
viewed in the formula bar - do not type these yourself. If you need to
edit or amend the formula, you need to use CSE again.

Hope this helps.

Pete

 

[Daryl S]

Jim -

If you want to sum the three conditional parts, then here is what it looks
like:

=IF(B2<167,25,B2*0.15)+IF(B2<400,20,B2*0.05)+IF(A2="October",14,0)

I am not sure about where the 'number of Octobers' comes from, so I just
added the third piece to calculate as 14 if the month is October, and 0 if
not.

Is that what you are asking for?