How can I shorten a very long formula?

[Tompanilla]

Hi!

I have a very long formula consisting of 140 possibilities for each cell.
Is it possible to shorten it so its more easy to programme and check?

Every cell in A1 to A20 has to be checked for a number (1-20) and beside that it has to be decided which weekday cell B is.

=IF(A13=1&IF(B13="monday";K2;"")&IF(A13=1&OM(B13="tuesday";K3;"") aso.

Thanks for any suggestion

 

[Tompanilla]

Hi!

I have a very long formula consisting of 140 possibilities for each cell.
Is it possible to shorten it so its more easy to programme and check?

Every cell in A1 to A20 has to be checked for a number (1-20) and beside that it has to be decided which weekday cell B is.

=IF(A13=1 ; )&IF(B13="monday";K2;"")&IF(A13=1 ; )&OM(B13="tuesday";K3;"") aso.

Thanks for any suggestion

There seems to be smileys inserted instead of ; )

 

[schielrn]

I don't think you should have a ; after the 1 and then a closed parentheses for one thing it is making a winking face. Put some spaces in there or put it in code tags and it might be easier to see what you are after.

And maybe can you explain exactly what you want to accomplish and maybe a screen shot or just paste some cells in here of what your data looks like?

Thanks.

 

[schielrn]

Also I don't think you want to use the & ampersand? Are you looking if A13 is 1 and B13 is Monday, return K2? That would be something like:

=if(and(a13=1;b13="Monday");k2;"")

Hope that helps start and allows you to explain exactly waht you are after.

 

[nikeshtnt]

Hi Tompanilla,

Can you provide me some attachment so that I can see how your data look like i.e. row & column used in the formula?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Because I think your formula can be shorten by If and And function.<o></o>
<o></o>
Moreover I am new to this forum so can you guide me how to post my queries about excel. I am already learned lot in excels and now want to enhance it.<o></o>
<o> </o>

 

[Tompanilla]

My sheet looks like this:

A13 =day of month (eg. 2009-04-01)
B13 = weekday (eg. wednesday
C13 = workingpass (eg. 7)
D13 = workingpasstime (eg. 07:00-17:00)
E13 = workinghours (eg. 10)
F13 = note (eg. mondays 5:00-17:00)
G13 = extra (eg. jour)

in cells below there are facts that should be automatically entered in D13-G13
depending on the number of the workingpass i C13 and the weekday in B13.

BA13 07:00-17:00 (workingpasstime)
BB13 10 (workinghours)
BC13mondays 05:00-17:00 (note)
BD13 jour (extra)

If workingpass is no 1 and weekday monday it should pick the right info.
Same if workingpass no 2 and weekday monday aso.
This means that there are 140 possibilities for each C13-G13.
(BA7 is for example workingpass no 1 and BA26 is workingpass 20)