Conditional Calculation 1

[benchod]

Hello all,

I use a spread sheet and I have to manually calculate a column using data from other columns. The spreadsheet looks like this:

Date Month Y/N Price Range Low High Qty P/U
18-Mar-02 3/12 Y 3.99 UTI 4.99 15 3.0
20-Jun-02 6/12 N 2.50 TOF 1.50 10 6.7

I need to calculate the last column (P/U), the way I do this is by:

Low/High - Price = X (unsigned).

Now, X * Oty = Y

Finally, Y / Low/High = P/U

It is necessary to recognise the Low or High and base the calculation on that. You will only ever have one or the other.

Thanks in advance for your help!

 

[euskadi]

I think I understand what you're asking... try this:

Price in cell D2
Low In F2 (if there is a low)
High in G2 (if there is a high)
Qty in H2
In I2 enter this equation

   =H2*ABS(MAX(F2:G2)-D2)/MAX(F2:G2)

 

[benchod]

Thanks for your response.

I'm new to VBA and not quite sure how to code this.

Each day I put in more than one line at the top of the sheet. Will this have any impact?

 

[benchod]

Anyone else have any further suggestions on this, please?

 

[SkipVought]

Hey BenChod,

euskadi gave you a great spreadsheet solution, NOT VBA. You just enter the formula in I2 and VOLA, your result based on high/low data.

Be sure to recognize euskadi's contribution if you feel that it is a helpful or expert post! by clikcing the hyperlink in the lower left-hand corner of euskadi's post.



Skip,
metzgsk@voughtaircraft.com

 

[DaleWatson123321]

Hey Skip,

Great to see you are "nudging" newcomers regarding the importance of showing "proper recognition" by issuing "STARS".

However, you probably didn't notice that BenChod is a "visitor", and as you know "visitors" don't have the option to issue "STARS" - the hyperlink in the lower-left-corner does NOT exist UNTIL one becomes a MEMBER.

Advice to BenChod, therefore, should be for him to become a MEMBER. THEN, he'll be in a position to issue STARS for any FUTURE postings.

I've therefore gone ahead and issued euskadi a STAR.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[benchod]

STAR, STAR, STAR, STAR.!!!

Firstly, may I complement all in this Forum on their skills and solutions. I am a recent user and think the site highlights the vast capability of the programmers featured in this forum.

Secondly, I am still having trouble with programming this! I have numerous worksheets that look like the example above. Somebody suggested having one control and passing all the sheets through this.