Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Zhekka
 
Posts: n/a
Default apply arithmetic functions on special multiple cells

Hey all

I've searched in the group history and haven't found the exact answer
for my problem.

I have an array of cells, each cell is calculated from the previous
one(s) - but not always in the same way (it's not always the same
formula)

from each cell's VALUE i want to substract 1 and then multiply by 100


I tried the special paste feature, but it's problematic, since it
changes the value of the cell, and the next cell's value is changed
(here's an example)

A1 A2 A3
1.2 1.4 1.2

A1: 1.2
A2: = A1+0.2
A3: = A2-A1+1

Here are the desired results:
A1 A2 A3
20 40 20

Now, when applying that special paste, i will get unwanted results:
A1: 20 (as expected)
A2: 1920 ((20+0.2)-1)*100
A3: 190000

I hope I explained myself clearly.

Is this possible?

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Rob van Gelder
 
Posts: n/a
Default apply arithmetic functions on special multiple cells

You're on the right track with the paste special subtract and multiple.
Treat that as steps 2 and 3.

Step 1 as follows:
Copy the cells. Paste Special Values

--
Rob van Gelder - http://www.vangelder.co.nz/


"Zhekka" wrote in message
oups.com...
Hey all

I've searched in the group history and haven't found the exact answer
for my problem.

I have an array of cells, each cell is calculated from the previous
one(s) - but not always in the same way (it's not always the same
formula)

from each cell's VALUE i want to substract 1 and then multiply by 100


I tried the special paste feature, but it's problematic, since it
changes the value of the cell, and the next cell's value is changed
(here's an example)

A1 A2 A3
1.2 1.4 1.2

A1: 1.2
A2: = A1+0.2
A3: = A2-A1+1

Here are the desired results:
A1 A2 A3
20 40 20

Now, when applying that special paste, i will get unwanted results:
A1: 20 (as expected)
A2: 1920 ((20+0.2)-1)*100
A3: 190000

I hope I explained myself clearly.

Is this possible?



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Zhekka
 
Posts: n/a
Default apply arithmetic functions on special multiple cells

No... it doesn't work :(

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default apply arithmetic functions on special multiple cells

You have to maintain the multiplier, so you probably need helper columns

B1: =(A1-1)*100
B2: =((B1+0.2)-1)*100
B3: =((((B1+0.2-1)*100)-(A1-1)*100+1)-1)*100


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Zhekka" wrote in message
oups.com...
No... it doesn't work :(



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Zhekka
 
Posts: n/a
Default apply arithmetic functions on special multiple cells

So, you're saying there's no other way rather than using new
columns/rows

(This will be problematic, since I already have a chart out of those
values.....)

Bob Phillips wrote:
You have to maintain the multiplier, so you probably need helper columns

B1: =(A1-1)*100
B2: =((B1+0.2)-1)*100
B3: =((((B1+0.2-1)*100)-(A1-1)*100+1)-1)*100


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Zhekka" wrote in message
oups.com...
No... it doesn't work :(




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default apply arithmetic functions on special multiple cells

There may be, but I cannot see it I am afraid.

Bob

"Zhekka" wrote in message
oups.com...
So, you're saying there's no other way rather than using new
columns/rows

(This will be problematic, since I already have a chart out of those
values.....)

Bob Phillips wrote:
You have to maintain the multiplier, so you probably need helper columns

B1: =(A1-1)*100
B2: =((B1+0.2)-1)*100
B3: =((((B1+0.2-1)*100)-(A1-1)*100+1)-1)*100


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Zhekka" wrote in message
oups.com...
No... it doesn't work :(




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
checking that cells have a value before the workbook will close kcdonaldson Excel Worksheet Functions 8 December 5th 05 05:57 PM
Use functions when cells are merged Amanda Excel Worksheet Functions 3 September 12th 05 06:08 PM
Formula to delete blank cells across multiple columns? SamFunMail Excel Worksheet Functions 2 September 1st 05 07:05 AM
How can I have excel search and add multiple cells to find a targe Blakepro Excel Discussion (Misc queries) 1 April 1st 05 02:37 AM
How do I apply a combo box to multiple cells in Excel so that it . DB Excel Discussion (Misc queries) 1 January 12th 05 04:42 PM


All times are GMT +1. The time now is 03:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"