IF funtion question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

wm - 26 Dec 2006 09:02 GMT

Hi all,

Kindly assist on the following:

eg: 10.00 per 1st 5 items, 5.00 per every additional 5 items
Thus, if the total is 20 items, then the answer would be 20.00

Pls adv on generating the formula for the above. Thanks in advance.

wm..

Reply to this Message

George Gee - 26 Dec 2006 09:54 GMT

Is your maths right?

=A1-5+10

George Gee

> Hi all,
>
[quoted text clipped - 6 lines]
>
> wm..

Reply to this Message

George Gee - 26 Dec 2006 10:03 GMT

To simplify:

=A1+5

George Gee

> Is your maths right?
>
[quoted text clipped - 12 lines]
>>
>> wm..

Reply to this Message

Muhammed Rafeek M - 26 Dec 2006 10:23 GMT

i guss ur eq is wrong. according to ur logic if the total is 20 items, then
the answer would be 25.00

however see the below mentioned foumula
=IF(A1>=5,10+INT((A1-5)/5)*5,10)

or

=IF(A1>=5,10+((A1-5)/5)*5,10)

u can try both formula

if u need further information, pls let me know

pls do rating

> Hi all,
>
[quoted text clipped - 6 lines]
>
> wm..

Reply to this Message

George Gee - 26 Dec 2006 10:41 GMT

Sorry, if someone could explain the original posters logic.
To me the answer to the original question is 25.

George Gee

>i guss ur eq is wrong. according to ur logic if the total is 20 items, then
> the answer would be 25.00
[quoted text clipped - 22 lines]
>>
>> wm..

Reply to this Message

kassie - 26 Dec 2006 13:40 GMT

Gee George, your right (vbg). I would suggest the OP post again with exactly
what he wants! 10+5+5+5=25, not 20. If the anser is to be 20, then some
logic is missing, so no one will really be in a position to help him! To try
and get to 20, is an excercise in futility, as it will not help the person.

Signature

Hth

Kassie Kasselman

> Sorry, if someone could explain the original posters logic.
> To me the answer to the original question is 25.
[quoted text clipped - 27 lines]
> >>
> >> wm..

Reply to this Message

George Gee - 26 Dec 2006 14:25 GMT

Kassie

As the OP has not replied to any posts in this thread,
one must assume that he is otherwise occupied, or that
the "20" was a typo!
Also, the formula(s) provided by "Muhammed Rafeek M",
will probably meet his requirements.

George Gee

> Gee George, your right (vbg). I would suggest the OP post again with
> exactly
[quoted text clipped - 36 lines]
>> >>
>> >> wm..

Reply to this Message

wm - 29 Dec 2006 09:31 GMT

Thanks all for the replies. Actually, there was typing error. the correct
anwer for the below (1st eg) is 25.

On top of the below requirement, there is another criteria as following:

If the charge for 1st set is 10.00, 5.00 per very additional set.
And 1 set can consist of 5 items.

Eg: if there are 17 items, then there should be 4 sets , so charges are 25.00
*eventhough the 4th set consists of 3 items only, but it will be charged as
5.00 for the particular set.

I have tried out this,

A1= no. of items

=IF(A1/5>1,ROUNDUP(A1/5-1,0)*5+10,10)

Wm.......

> Kassie
>
[quoted text clipped - 46 lines]
> >> >>
> >> >> wm..

Reply to this Message

CLR - 26 Dec 2006 19:21 GMT

Maybe.........

=TEXT(COUNT(A1:A20),"0.00")

Vaya con Dios,
Chuck, CABGx3

> Hi all,
>
[quoted text clipped - 6 lines]
>
> wm..

Reply to this Message