Help regarding a pricing sheet

Closed
Kev1027 - Aug 19, 2016 at 08:41 PM
Kev1027 Posts 2 Registration date Friday August 19, 2016 Status Member Last seen August 19, 2016 - Aug 19, 2016 at 10:55 PM
Hello,

I'm looking for help regarding a pricing sheet I'm trying to create on excel. I have my quantity in C5 = 2500 and cost in C15 = 75,000 if C5 <= 2500. I need for C15 to increase by 10,000 when C5 increase by 2500 intervals.

So if C5<=4900 C15= 75,000
If C5>5000 >7499 C15=85,000
If C5>7500 > 9999 C15 = 95,000
If C5 >10,000 > 11,999 C15= 105,000
ect....

4 replies

Blocked Profile
Aug 19, 2016 at 08:44 PM
Welcome Kev.....

The If formula is as such:

=IF(logic_test,true,false)

You can nest IF's (if this applies to you):
=IF(logic_test1,=IF(logic_test2,true2,false2),false1)

Let us know if you have any problems!

Have fun!

1
fdibbins Posts 33 Registration date Sunday June 19, 2016 Status Contributor Last seen November 20, 2016 1
Aug 19, 2016 at 10:40 PM
1st, I think you have the 2nd sign in your request the wrong way round. I think the 2nd sign should be less than, not greater than.

Anyway, try this...
=IF(C5>10000,105000,IF(C5>7500,95000,IF(C5>5000,85000,75000)))
1
Kev1027 Posts 2 Registration date Friday August 19, 2016 Status Member Last seen August 19, 2016
Aug 19, 2016 at 08:52 PM
Thanks for the quick response ac3mark,

I'm afraid I may need a bit more detail from you. Here is what I have so far:

=IF(C5<=4999,75000,75000+10000) and I am not sure how to set up from here so that what ever number they enter in C5, C15 increase appropriately.

Thanks!
0
Kev1027 Posts 2 Registration date Friday August 19, 2016 Status Member Last seen August 19, 2016
Aug 19, 2016 at 10:55 PM
Hello fdibbins,

Thanks for your response! You are correct, thanks for the correction!

This works perfectly, however, in C5 a number as high as 600,000 could be entered. Is there away to do this with out having to build each one of the references?

Thanks!
0