Results 1 to 4 of 4

Thread: Excel question

  1. Nov 25th, 2004, 10:49 AM #1
    krtxmrtz
    krtxmrtz is offline

    Thread Starter
    vbuggy krtxmrtz's Avatar
    Join Date
    May 2002
    Location
    In a probability cloud
    Posts
    5,573

    Excel question

    Hi folks,

    I don't know if this is an approppriate forum for this type of question but, anyway here it is.

    I have an Excel spreadsheet with a set of numbers in the range A5:A200 and another set of numbers in the range B5:B200. Then there's a formula in the range C5:C200 that squares all the numbers in the first column: A5*A5 in cell C5, A6*A6 in cell C6 and so on.

    Then I have an indexer number in cell A1. It can be 0 or 1. I want to change the formula in column C such that, if A1's contents is 0 then the formulas are A5*A5, A6*A6, etc and if the contents of A1 is 1, then the formulas in column C are B5*B5, B6*B6, etc. Of course, once I have the correct formula in C5 then I want to drag over the rest of the column.
    Lottery is a tax on people who are bad at maths
    If only mosquitoes sucked fat instead of blood...
    To do is to be (Descartes). To be is to do (Sartre). To be do be do (Sinatra)
    Reply With Quote Reply With Quote
  2. Nov 25th, 2004, 11:07 AM #2
    si_the_geek
    si_the_geek is offline
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,974
    the formula for C5 would be:

    =If($A$1=0,A5*A5,B5*B5)


    note that any value other than 0 will square column B
    (Microsoft MVP from July 2007 to June 2017) . . . . . . . . . . Hitchhiker's Guide to Getting Help at VBForums

    Database Development FAQs/Tutorials (updated May 2011) (includes fixing common DB related errors, and [VB.Net] ADO.Net Tutorial, and [Classic VB] ADO tutorial /further steps)
    other useful DB bits: . Connection strings (alternative copy).•. MDAC/Jet/ACE downloads .•. SQL Server downloads .

    Classic VB FAQs (updated Oct 2010) (includes fixing common VB errors)
    some of my Classic VB bits: . Tutorial: How to automate Excel from VB6 (or VB5/VBA) .•. SQL 'Select' statement formatter/checker .•. Convert colour number to colour name .•. FlexGrid: fill from recordset .•. FlexGrid: AutoSize columns .•. DB Reserved Words checker .
    Reply With Quote Reply With Quote
  3. Nov 25th, 2004, 12:19 PM #3
    krtxmrtz
    krtxmrtz is offline

    Thread Starter
    vbuggy krtxmrtz's Avatar
    Join Date
    May 2002
    Location
    In a probability cloud
    Posts
    5,573
    Thanks, I just can't believe it was this easy!

    The thing is I know the IF function and have often used it but for some reason I didn't think of it for this situation and spent plenty of time fooling around to no avail.
    Last edited by krtxmrtz; Nov 25th, 2004 at 12:36 PM.
    Lottery is a tax on people who are bad at maths
    If only mosquitoes sucked fat instead of blood...
    To do is to be (Descartes). To be is to do (Sartre). To be do be do (Sinatra)
    Reply With Quote Reply With Quote
  4. Nov 26th, 2004, 04:25 AM #4
    si_the_geek
    si_the_geek is offline
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,974
    lol.. we all have moments like that!
    (Microsoft MVP from July 2007 to June 2017) . . . . . . . . . . Hitchhiker's Guide to Getting Help at VBForums

    Database Development FAQs/Tutorials (updated May 2011) (includes fixing common DB related errors, and [VB.Net] ADO.Net Tutorial, and [Classic VB] ADO tutorial /further steps)
    other useful DB bits: . Connection strings (alternative copy).•. MDAC/Jet/ACE downloads .•. SQL Server downloads .

    Classic VB FAQs (updated Oct 2010) (includes fixing common VB errors)
    some of my Classic VB bits: . Tutorial: How to automate Excel from VB6 (or VB5/VBA) .•. SQL 'Select' statement formatter/checker .•. Convert colour number to colour name .•. FlexGrid: fill from recordset .•. FlexGrid: AutoSize columns .•. DB Reserved Words checker .
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules


Click Here to Expand Forum to Full Width