Check out some similar questions!

jimisugly · May 8, 2009, 08:41 AM

Hi,

I'm trying to get a cell to multiply a given value from another cell to show the answer.

Basically I'm trying to get it to show

If M5=1 then M6=3068
If M5=2 then M6=3068+3068/2 OR (3068+1534)
If M5=3 then M6=3068+3068
If M5=4 or above then M6=3068+3068+3068/2 OR (3068+3068+1534)

Can anyone tell me the formula I need as my head is spinning round from staring at this for 3 days!!

Zazonker · May 8, 2009, 09:42 AM

Well, there's more than one way to do this, but assuming that you have good reason for the layout of the sheet of which this is a part, the following will do what you want. I put the '/2' version, just to make it track with your definition and to help for an alternative discussed at the end of this message. This is the equation to put in M6:

=IF(M5=1,3068, IF(M5=2, 3068+3068/2, IF(M5=3, 3068+3068, 3068+3068+3068/2)))

The implicit assumption here is that M5 will always be an integer and will not be negative. If these aren't true, a couple more nested if's could be added.

A couple of comments:
Personal preference (probably from programming training/experience), I don't like putting values into equations anymore than I have to - it makes you look at the equation to see what you did. I'd pick an area on the spreadsheet for my constants and label them, and then use the cell reference instead of the number in the equation. Sometimes, it's even better to name the cells and use the name in the formula.

In this case, say 3068 is something called "truck", you label a cell in your constant definition area "truck", store 3068 in it, and then put "truck" (without the quote marks) everyplace in the formula where 3068 appears.

Hope this helps.

JBeaucaire · May 8, 2009, 10:24 AM

Originally Posted by jimisugly

If M5=1 then M6=3068
If M5=2 then M6=3068+3068/2 OR (3068+1534)
If M5=3 then M6=3068+3068
If M5=4 or above then M6=3068+3068+3068/2 OR (3068+3068+1534)

To avoid having to calculate IFs over and over, and since these value appear to be constants, my take would be:

=LOOKUP(M5,{0,1,2,3,4},{0,3068,4602,6136,7670})

The LOOKUP() function uses "fuzzy math" so any numbers missing during the lookup will round DOWN to the next nearest number. So, anything above 4 will keep using 4.

Any values less than 0 would give an error. I imagine you could solve that by changing the first 0 to something less than zero, like -10000. But that may not be necessary based on how your cell M5 is getting its value.