Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Excel sheet formula

0

how can I accomplish this.

have two columns of data one date the other sine wave data.

i want to write date and related data only once when slope of data changes and ignore fallowing data and date until slope changes again and write only once that date and data point once's again.

if I write =if(slope>0,date,"")or =if(slope<0,date,"") it keeps writing date till slope changes.

i only want date and related data only once and ignore the rest till slope changes again.

how to make it write related data with date only one time till slope changes agai in excel sheet not VBA formula?

is this possible in excel sheet?

excel
MS Office Version: 2013
posted by: Naem on Mon Jul 2, 2018 at 4:35 am
Answer
Discuss

Discussion

Even though it may seem scary at first, VBA and Macros really are the way to go here.
don (rep: 1989) Jul 2, '18 at 9:42 am
Add to Discussion

Answers

0

Worksheet formulas can't write anything to a worksheet. Only the user can do that or a computer progam (f.i. VBA) which can act on behalf of the user.

A formula like =IF(Slope>0,Date,"") can monitor another cell, in this case the cell which contains the value for Slope. This can be set up. However, it isn't possible to write only if there is a change: it either writes or it doesn't write.

One might think of writing a value like 1 or 0 (True or False) to yet another cell to indicate if the Slope value was written once, and let the formula monitor both the Slope cell and the one that indicates whether a date was written before. That is possible, too. However, the cell indicating True or False would need to contain a hard value, not a formula, and the only way to enter a hard value is for the user to do it - or a computer program acting on behalf of the user.

A program to log changing values of a cell is pretty much standard fare for VBA. Why is it that you want to avoid using it?

posted by: Variatus (rep: 4889) on Mon Jul 2, 2018 at 5:21 am
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login