Evaluate expression in RC notation

**Dream Whisperer** · 03-22-2011, 07:32 AM

I would like to embed a formula which returns an integer inside RC notation eg.

R[RowVar]C, where RowVar=6*INT((R-1)/6)

So far I keep getting an error message saying that a formula has been entered incorrectly. Is there a way of doing this?

Any assistance would be very much appreciated.

**pike** · 03-23-2011, 12:57 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**Dream Whisperer** · 03-23-2011, 08:34 AM

Thank you pike for your reply and for your suggestion.

I have attached a file Sample.xls containing an example of data and also the structure. I am trying to find all the possible combinations of values that result from subtracting the given 6-number sets along the top row from each other as shown in the leftmost column.

The subtractions occur between corresponding elements in each 6-number set - so for example the third element of any 6-number set along the top of the page is always subtracted from the corresponding third element in a given target set in the first column. Obviously when a set is subtracted from itself a set of zeros results along the diagonal, which are shaded.

Clearly the sets of results below the shaded zero results are just the negative of symmetrically located counterparts above that diagonal. They are included for clarity only.

This is only an example. The real thing has about 200 6-number sets each way so there are 20,000 possible 6-number sets to be calculated above the shaded diagonal.

At the moment I am cutting and pasting the formula :

RC1-R[-n]C,

where n is a multiple of 6 which I manually adjust when I reach a row which is a multiple of 6.

I would like to be able to do the following :

RC1-R[-6*INT((R-1)/6)]C

as this would save the cutting and pasting. Unfortunately I keep getting an error message saying that a formula was entered incorrectly.

I had thought that maybe it was possible to somehow do this :

RC1-R[RowVar]C,

where RowVar = -6*INT((R-1)/6)

I hope I have made myself clear. I would be very grateful for any suggestions. I am willing to accept that perhaps I am barking up the wrong tree altogether as I have only just started using Excel.

**pike** · 03-24-2011, 12:47 AM

Hi Dream Whisperer,
Most do use RC references in worksheets
But if you place the six formula into the area

Please Login or Register  to view this content.

then highligh the six cells and drag down or across the RC reference forumula will be repeated

**Dream Whisperer** · 03-24-2011, 02:51 AM

Hi pike

Your solution works perfectly. Now I can fill huge swathes of the spreadsheet rapidly by filling and dragging as you suggested.

Many thanks for taking the time to help me with this issue.

All the best,

Dream Whisperer