IF formula that also multiplies

**amy22x3** · 10-10-2007, 08:52 AM

I'm trying to get an IF formula to produce a result depending on what is in B12 (please see formula below). While this formula works, it is limited to 10 nested formulas and I may need to go up to 25. The purpose of the formula is to add $1500 for each occurence after 3. For example if the client has 1-3 systems, the flat fee is $10,000, each system after that is going to cost them $1500 per. How do I write this?

=IF($B$12<=3,3500,IF($B$12=4,4500,IF($B$12=5,6000,IF($B$12=6,7500,IF($B$12=7,9000,IF($B$12=8,10500,IF($B$12=9,11500,IF($B$12=10,13000))))))))

**NBVC** · 10-10-2007, 09:03 AM

How about:

=IF($B$12<=3,3500,3000+1500*($B$12-3))

**oldchippy** · 10-10-2007, 09:17 AM

Or this,

Please Login or Register  to view this content.

**amy22x3** · 10-10-2007, 09:30 AM

NBVC - What's the 3000 being added to the 1500 in your formula?

**robert111** · 10-10-2007, 09:33 AM

or you could make yourself a lookup table

0....0
1.....0
2.....0
3.....3000
4......4500
5......6000

etc etc

=vlookup(your cell reference, lookuptable,2)

**NBVC** · 10-10-2007, 09:36 AM

Originally Posted by amy22x3

NBVC - What's the 3000 being added to the 1500 in your formula?

The 3000 is actually being added to this: 1500*($B$12-3)

which would result in 4500 when B12=4, 6000 when B12 = 5, etc... as per your original formula....

are those the results you want or are you looking for final number, e.g. =IF($B$12<=3,0,1500*($B$12-3))+10000 would result in 10000 for 3 or less, 11500 for 4, 13000 for 5, etc...

IF formula that also multiplies

LinkBack

Thread Tools

Rate This Thread

Display

IF formula that also multiplies

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions