Excel question

[Viney]

Ok Boffins, i need a formula writing. I have tried and failed.

I am doing print volumes. Data is in 2 columns

Column A= B&W Volume
Column B= Colour Volume

Four SLA Bands

A - 4 Hour - B&W Between 0-2000 Colour Between 0-500
B - 8 Hour - B&W Between 2001-2500 Colour Between 501-1000
C - 16 Hour - B&W Between 2501-10000 Colour Between 1001-2000
D - Negotiated - B&W Between 2001+ Colour Between 1000+

So result is If a volume falls into one of the above catagories then the correct SLA is shown in Column C. But, heres the thing. It has to compare the 2 values. If say the colour volume falls into the higher catagory then this is what needs to be shown and vice versa.


I thinks thats pretty clear?

Help

[markc123]

I can see a possible way, but its a horrible hack using a construct like this:

=IF(D4<2000,1,(IF(D4<2500,2,IF(D4<10000,3,4))))

This will return 1 2 or 3 for band A B C for B+W

=IF(D5<500,1,(IF(D4<1000,2,IF(D4<2000,3,4))))

This will return 1 2 or 3 for band A B C for Colour

Then MAX(D6:D7) will give you the highest band.

An other IF bodge can magic 1,2,3 into A,B,C

Band D - seems to intersect with other bands so its trick to get a formula to take into account of this.

[SoulKiss]

I assume the negotiated you give is wrong and that you mean everything over the 16 Hour becomes negotiated.

If this is the case, PM me an email address and I'll send you the file, its set up so that you can change the bands.

EDIT

You can download it from here. (http://forums.sv650.org/cookbook.chaoscrypt.com/Viney.xls)

The formula gets the volume limits and the SLA's from the table to the right, so you can adjust it as you need.

[Sabarius]

Aw shucks you beat me to it, I love a good spreadsheet me.

[Viney]

In the end, it was this badboy that i sorted
=IF(AND(H2>=$Q$4,H2<=$Q$5),$Q$2,(IF(AND(H2>=$Q$9,H2<=$Q$10),$Q$7,(IF(AND(H2>=$Q$14,H2<=$Q$15),$Q$12,$Q$17)))))