Need a help with the IF/ IFCOUNT function

[Xenos]

I am designing a quote sheet where different markups will be placed
depending on different value.

As an example:

If a product is between 0 and 19.99 I want to mark it up 100%
If a product is between 20.00 and 49.99 I want to mark it up 60%
If a product is between 50.00 and 99.99 I want to mark it up 40%
If a product is over 100 I want to mark it up 35%

My actual price box will be lets say A1
My between price boxes will be B1,B2… B3,B4…B5,B6…B7
My Markups will be in C1,C2,C3,and C4.

And my result will be in D1.

Ok this is all hypothetical but you get the jest.

Can I write a singe code in box D1 to do the calculations or do I need
to break the code up with 4 different expressions?

Can someone just get me started with the code like =IF(A1>B1 but <B2
use C1) ….

I can do all the basic stuff I just don’t know where to start with this
one.


Thanks

Todd

 

[ronthedog]

I am designing a quote sheet where different markups will be placed
depending on different value.

As an example:

If a product is between 0 and 19.99 I want to mark it up 100%
If a product is between 20.00 and 49.99 I want to mark it up 60%
If a product is between 50.00 and 99.99 I want to mark it up 40%
If a product is over 100 I want to mark it up 35%

My actual price box will be lets say A1
My between price boxes will be B1,B2… B3,B4…B5,B6…B7
My Markups will be in C1,C2,C3,and C4.

And my result will be in D1.

Ok this is all hypothetical but you get the jest.

Can I write a singe code in box D1 to do the calculations or do I need
to break the code up with 4 different expressions?

Can someone just get me started with the code like =IF(A1>B1 but <B2
use C1) ….

I can do all the basic stuff I just don’t know where to start with this
one.


Thanks

Todd

You can use AND as follows to combine multiple conditions

=IF(AND(A1>20, A1<30),A1*B1, A1*C1)

or you can nest IF statements like this

=IF(A1>10, IF(A1>20,A1*B2,A1*A2),A1)

Use the evaluate formula command if you need to step through a long-ish
IF function (as these can end up)

 

[agarwaldvk]

Dear Todd

This is one of solutions :-

In cells A1, A2, A3 and A4 enter the values of price barriers viz 100
99.99, 49.99 and 19.99 (these values must necessarily be in th
descending order)

In cells B1, B2, B3 and B4 enter the corresponding values for th
markups viz 35, 40, 60 and 100.

Now if you have the product names in cells A5 through to A500, thei
actual prices in cells B5 through to B500, then you could enter th
following formula in cell C5 and drag it all the way down to cell C50
:-

Index($A1:$B4, Match(B5,$A1:$A4,-1),2)

Hope this helps!


Best regards


Deepak Agarwa

 

[Xenos]

Thanks Guys for the tips, I got it figured out ( with your help of
course). Now I’m hoping you can help with another issue.

This is my formula , and as you can see it’s quite long.

=IF(G29>Markups!C11,G29*B29*Markups!F11+G29*B29*Markups!F12,IF(G29>Markups!C10,G29*B29*Markups!F11,IF(G29>Markups!C9,G29*B29*Markups!F10,IF(G29>Markups!C8,G29*B29*Markups!F9,IF(G29>Markups!C7,G29*B29*Markups!F8,G29*B29*Markups!F7)))))

Now I want to copy this into many cells on my quote sheet.

B29 is my quantity
G29 is the price
K29 is my formula

What my goal is, copy the formula to cells K30,K31+ and so on. Each
time my targets will also change to B30+ and G30+. If I copy and past
the formula the targets remain at B29 and G29. is there an easy way to
copy this formula and also change the targets to the same row as the
formula?

 

[Xenos]

Deepak Agarwal, your one step ahead of me...Ofcourse I will think
about what you mention in your post and play with it.