Mutiplying Given number with parts (first 2 , next 2 , next next 2 , remaining ...)

[sqlindia]

Hi Experts,

I need a formula for below logic , please help me
Excel_Calc_2.xlsx
Assume if cell c16 value 'X' integer
then first 2 of 'X' should be multiplied with $50
Next 2 of 'X' should be multiplied with $40
Next Next 2 of 'X' should be multiplied with $30
and rest of the 'X' values should be multiplied with $20.

Please find the attached excel sheet for an example and details.
The cells marked in green are the only cells you need to add formulas.

Thanks in advance

Thanks,
Naveen

[6StringJazzer]

Your spreadsheet doesn't quite match what you described so I changed it for

0-2
3-4
5-6
7-999 (arbitrary absolute maximum makes things a little more flexible)

Also, instead of describing those ranges as text, I made them numbers you can actually calculate using them.

See attached.

[dredwolf]

Assuming you ACTUALLY wanted 0 orders to = 2
In C16:
=IF(C15<2,2,ROUNDUP(C15,0))
Or , if only orders >0
C16:
=IF(AND(C15>0,C15<2),2,ROUNDUP(C15,0))

In C19:
=IF($C$16,2*B19,0)

in C20:
=(($C$16>(ROWS($C$19:C19)*2))+($C$16>(ROWS($C$19:C19)*2+1)))*B20+IF(AND($C$16>8, ROWS($C$19:C19)=3),($C$16-8)*B20,0)
Drag to C22

Hope this helps

[sqlindia]

excellent dredwolf , Thanks mate

I am going through the formulas given by you.

Thank,
Naveen

Assuming you ACTUALLY wanted 0 orders to = 2
In C16:
=IF(C15<2,2,ROUNDUP(C15,0))
Or , if only orders >0
C16:
=IF(AND(C15>0,C15<2),2,ROUNDUP(C15,0))

In C19:
=IF($C$16,2*B19,0)

in C20:
=(($C$16>(ROWS($C$19:C19)*2))+($C$16>(ROWS($C$19:C19)*2+1)))*B20+IF(AND($C$16>8, ROWS($C$19:C19)=3),($C$16-8)*B20,0)
Drag to C22

Hope this helps

[dredwolf]

You are welcome