Excel Function ?

[PardeGT]

Is there a shorter way to write the below formula? I will need to use this similar function in many cells within a spreadsheet and was curious if there is a shorter way of typing it. Sorry, but I only know the basics and have not needed much beyond simple functions before.

=IF(A1=1,B5/1*1,IF(A1=2,B5/2*1,IF(A1=3,B5/3*1,IF(A1=4,B5/4*1,IF(A1=5,B5/5*1,IF(A1=6,B5/6*1,0))))))

Thanks for your replies.

[PMich]

I want to make sure I understand what you are trying to do. In your destination cell (the cell you are typing the formula in) you want the value of b5 to be divided by the value in a1. If a1 is a value other than 1 to 6 you want the value in the destination cell to be zero. I don't see why you are multiplying everything by 1 so I omitted it.

=IF(1<=A1,IF(A1<=6,B5/A1,0))
In english, if 1 is less than or equal to A1 and A1 is less than or equal to 6, then the value of the destination cell is equal to B5 divided by A1. If A1 is less than 1 or greater than six then value of destination cell is zero.

You may already know this but if you are doing this in multiple cells you can lock in a reference cell, row, or column by putting a dollar sign in front of the cell name in the formula. For example:

$A$1 in a formula will refer to that cell no matter where you paste it in the spreadsheet.

$A1 will lock in the column

A$1 will lock in the row

I find this really useful when I don't want to retype or copy and paste formulas all the time. HTH

[HyperTF]

How about this...

=IF(A1 >= 1, IF(A1<=6,B5/A1,0),0)

It does the same as PMich's, but if there is a possibility that the number will be less than 1 in A1 it will return a value of zero instead of FALSE.

Depends as PMich said as to the purpose!

[PMich]

When nesting if statements in excel you only need to supply one value if any of the nested logical statements are false. The way I wrote it should return a value of zero if A1 is less than 1. HyperTF, it appears to me that the way you wrote the if statement that there are too many arguments.

[PardeGT]

Thanks. First of all you are right PMICH in that I do not need the *1 in each function.

Here's the situation:
What the function is trying to do is there is a primary control cell (A1) with a entered value between 1 and 6 (mon-sat). There will then be another cell (B5) with a number in it. A third cell (C1) is what the function is written for. If A1 = 1 then C1=B5, if A1 = 2 then C1 =B5/2....etc up to A1 = 6 with C1 = B5/6. I will then create a similar formula for C2,C3,C4,C5 and C6. If, for example, there are only 4 days in the week (A1 = 4) then each funtion for C1,C2,C3 and C4 will return the value of B5/4 and C5 and C6 would be zero.

Thanks for your input.

[PMich]

Now I see where you are going. The formula I wrote would be for cell C1. You can copy and paste the formula into each of the above cells and increase the minimum by 1 to do what you want so:
C2 would be =IF(2<=A1,IF(A1<=6,B5/A1,0))
C3 would be =IF(3<=A1,IF(A1<=6,B5/A1,0))
C4 would be =IF(4<=A1,IF(A1<=6,B5/A1,0))
C5 would be =IF(5<=A1,IF(A1<=6,B5/A1,0))
C6 would be =IF(A1=6,B5/A1,0))

[PardeGT]

Thanks for your help. I was a little wrong once I looked at my instructions. I wanted it to accumulate the results from day to day. For example, on a 4 day week C1 would return B5/4*1, C2 = B5/4*2, C3 = B5/4*3 and C4 = B5/4*4.

HyperTF appears to be correct to have ,0),0) because otherwise when A1 is less than day count...such as answer in C3 = FALSE when A1 = 2. I want it to return a 0 instead.

Just to show you what I ended up with here is the function that works correctly for C4:
=IF(4<=$A$1,IF($A$1<=6,$B$5/$A$1*4,0),0)

Thanks again.

[PMich]

Originally Posted by PardeGT HyperTF appears to be correct to have ,0),0) because otherwise when A1 is less than day count...such as answer in C3 = FALSE when A1 = 2. I want it to return a 0 instead.

I didn't know you could do that. Looks like I learned a little something, too. IMHO, IF statements are one of the most useful things in excel and learning to use them can make life a lot easier.