# How can I shorten a very long formula?

#### Tompanilla

##### New Member
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

Last edited by a moderator:

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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 ; )

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.

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.

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>

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)

Replies
6
Views
140
Replies
6
Views
343
Replies
1
Views
310
Replies
2
Views
358
Replies
4
Views
434

1,207,436
Messages
6,078,539
Members
446,345
Latest member
MicCh

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back