Quick Excel Help Page 5

  • Lutz 29 Jan 2009 12:16:37 48,870 posts
    Seen 4 years ago
    Registered 18 years ago
    Then something else is wrong, cos it worked here. I chucked that formula in cell G32, and chucked numbers into G33. If the total of G33 hit -470389, it stops at 80,000.

    Did you copy and paste that formula? And what have you got in G33?

    Edit: Dunno where I got G344 from. O_o
  • Huntcjna 29 Jan 2009 12:18:27 13,890 posts
    Seen 3 years ago
    Registered 17 years ago
    Its my fault lutz, needed the result at -80000 not 80000.

    Fixed with a couple of tweaks to your formula, thanks fella. :D
  • Lutz 29 Jan 2009 12:20:40 48,870 posts
    Seen 4 years ago
    Registered 18 years ago
    Dur. :p

    Take it you removed the - from the -0.17 ?
  • Huntcjna 29 Jan 2009 12:22:26 13,890 posts
    Seen 3 years ago
    Registered 17 years ago
    This is the revised version:

    Gah can't show you the revised version because of the stupid bracket posting rules :(
  • Lutz 29 Jan 2009 12:23:43 48,870 posts
    Seen 4 years ago
    Registered 18 years ago
    Um, that's wrong! :D

    Surely all you need to do is remove that minus though?
  • Clive_Dunn 29 Jan 2009 12:23:52 4,862 posts
    Seen 2 years ago
    Registered 18 years ago
    =IF(SUM((G33-2000)*0.17)>80000,80000,(SUM((G33-2000)*0.17)))

    Seems to work, possibly a brackets issue ?
  • Lutz 29 Jan 2009 12:24:55 48,870 posts
    Seen 4 years ago
    Registered 18 years ago
    What Clive said.
  • Huntcjna 29 Jan 2009 12:25:30 13,890 posts
    Seen 3 years ago
    Registered 17 years ago
    I put a minus in front of the 80,000 and swopped > for the reverse symbol.

    Works a treat. :)
  • Lutz 29 Jan 2009 12:27:43 48,870 posts
    Seen 4 years ago
    Registered 18 years ago
    Heh. Works yeah, but minus a minus... excess symbols! :p

    Doesn't matter really. :)
  • James-Caldari 29 Jan 2009 13:03:26 2,103 posts
    Seen 10 years ago
    Registered 18 years ago
    Guys, I have a couple of Excel questions... what formula could I write to extract the year from a column containing dates in dd/mm/yyyy format. Also, how would I convert dates in American format, or UK format?
  • Lutz 29 Jan 2009 13:05:51 48,870 posts
    Seen 4 years ago
    Registered 18 years ago
    =YEAR(x) where x is your cell number with the date.

    And convert dates from American or UK into what...?
  • James-Caldari 29 Jan 2009 13:21:14 2,103 posts
    Seen 10 years ago
    Registered 18 years ago
    Cheers Lutzy.

    The latter should have read TO UK format FROM American format (or vice versa).
  • VeniVidiFlati 29 Jan 2009 13:55:55 25 posts
    Seen 11 years ago
    Registered 15 years ago
    You can do that just by formatting the cells. Go into 'Format Cells' and on the number tab, click on 'custom'. Then if you type in dd/mm/yyyy it will format it as UK style, if you type in mm/dd/yyyy it will be US.

    If you need to have both versions displayed in different columns for any reason, you can just have one column linked to the other, and have the formatting different in each. The data in the background will be the same (excel treats dates as just a number), they will just be displayed differently
  • Lutz 29 Jan 2009 14:10:27 48,870 posts
    Seen 4 years ago
    Registered 18 years ago
    NP James. I learnt that one here too!
  • X201 29 Jan 2009 14:15:04 22,082 posts
    Seen 2 hours ago
    Registered 15 years ago
    VeniVidiFlati wrote:
    You can do that just by formatting the cells. Go into 'Format Cells' and on the number tab, click on 'custom'. Then if you type in dd/mm/yyyy it will format it as UK style, if you type in mm/dd/yyyy it will be US.


    and if you want the month in letters to avoid confusion just type 3 m's instead of two.
  • opalw00t 4 Feb 2009 16:20:54 12,836 posts
    Seen 18 hours ago
    Registered 17 years ago
    This is tricky to explain (and might not work):

    Select an empty cell and set i to =B2/1.175 (assuming B2 is your top left data entry)

    Then select the cell and drag the little blob in to bottom right corner of the cell and fill the formula up and across so that it is the same area as your original data (i.e. you now have a big square of data/1.175).

    Copy the new data and paste as values over the initial data.

    Maybe.
  • Jeepers 16 Jun 2009 11:10:26 16,612 posts
    Seen 6 hours ago
    Registered 16 years ago
    FuckingbollocksingstupidingExcel...

    So, I'm doing some UAT work, and I need to record a list of defects. To make it easy for the poor sap who'll be fixing stuff, I wanted to space out rows, so as to make the Excel spreadsheet clearer to read.

    How do I apply the equivalent of padding above and below all cells in a sheet? I can see that I can set the heights of rows individually or on mass, but I want to have vertical padding applied to all cells, not manually set each row.

    Anyone help me? Virtual Jaffa in it for you.
  • mrpon 16 Jun 2009 11:22:09 37,316 posts
    Seen 3 hours ago
    Registered 15 years ago
    I'm lost, what do you mean by vertical padding? A blank column inbetween each column??
  • Jeepers 16 Jun 2009 11:44:47 16,612 posts
    Seen 6 hours ago
    Registered 16 years ago
    Yup, that's pretty much it. Imagine a carriage return above and below all the rows, so each is spaced out from the others. So it would be like this:

    Row 1 text lorem ipsum dolor

    Row 2 text lorem ipsum dolor

    Row 3 text lorem ipsum dolor

    rather than this:

    Row 1 text lorem ipsum dolor
    Row 2 text lorem ipsum dolor
    Row 3 text lorem ipsum dolor

    Does that make sense?

    Ta!
  • mrpon 16 Jun 2009 12:32:05 37,316 posts
    Seen 3 hours ago
    Registered 15 years ago
    How about:

    - highlight all the data (top left button)
    - right click Format Cells
    - Alignment tab, set Vertical alignment to Center, press OK
    - then with all cells still highlighted resize row height

    Voila?
  • mrpon 16 Jun 2009 12:34:05 37,316 posts
    Seen 3 hours ago
    Registered 15 years ago
    PS: You do know the difference between horizontal and vertical? ;)
  • Jeepers 16 Jun 2009 12:48:10 16,612 posts
    Seen 6 hours ago
    Registered 16 years ago
    Cheers mrpon - I don't think that'll do it - I have cells of differing heights, and I need vertical alignment to be at the top. Short of adding a carriage return (alt-enter) in front of each text block, I can't see a solution.

    Google suggests that Excel is just shit.

    mrpon wrote:
    PS: You do know the difference between horizontal and vertical? ;)

    'ere, you cheeky git! Vertical padding is the right term - my inner HTML grunt tells me so :)

    I figured that as you could indent-left and indent-right, you'd be able to add row-wide vertical padding. Of course, I forgot that Office is a big fat bag of shit.
  • mrpon 16 Jun 2009 13:01:03 37,316 posts
    Seen 3 hours ago
    Registered 15 years ago
    :D How about using something like =A1 & Char(10)
  • Jeepers 16 Jun 2009 13:19:01 16,612 posts
    Seen 6 hours ago
    Registered 16 years ago
    mrpon wrote:
    :D How about using something like =A1 & Char(10)

    Ooh, that sounds clever.

    I mean, I've no idea what you're talking about, but it sounds clever!

    Any more info for an Excel dunce like me?

    Ta for your help here fella.
  • mrpon 16 Jun 2009 14:16:28 37,316 posts
    Seen 3 hours ago
    Registered 15 years ago
    Ok I'm working blind here so try this sample and see if it's what you're after:

    - new worksheet
    - enter some text in cell A1
    - in cell B1 enter this formula =char(10) & A1 & char(10)
    - it'll look like nothing has happened
    - highlight all cells (top left button)
    - right click, format cells, alignment, tick wrap text
    - row 1 should be expanded now to have the carriage return above and below your text

    Obviously play around with this and see if you can make it work with your main spreadsheet.
  • Deleted user 17 June 2009 16:54:29
    I was going to post what mrpon said, except editing a cell, say H1, and pressing Alt+Enter there, then Enter to accept it. That gets you the "new line" char.

    Then using the formula =$H$1 & sourceCell & $H$1 where you want the text of soureCell appear as:

    ----

    text

    ----
  • Jeepers 17 Jun 2009 17:37:28 16,612 posts
    Seen 6 hours ago
    Registered 16 years ago
    Cheers both, altho' I'm on a poncey Mac and Alt-Enter does nuffink. I think I've resigned myself to it looking cramped.

    Fuck 'em. Serves them right for being testers...
  • mrpon 18 Jun 2009 09:43:08 37,316 posts
    Seen 3 hours ago
    Registered 15 years ago
    Apparently it's option-command-enter on a Mac.
  • localnotail 15 Jul 2009 14:23:36 23,072 posts
    Seen 8 years ago
    Registered 13 years ago
    My spreadsheet memory appears to have gaps in it through lack of use.

    Please can someone tell me the formula to place a numerical value in Column E to reflect the word value given in column D?

    detail: I am doing our first and last ever stocktake prior to selling off our business. I need to generate a depreciation code that can be used as a factor in valuing the stock. I have the following stock condition values: As New, Good, Fair, Rough, Kaput. I could just assign these values as a number to start with but my partner needs to at least understand the spreadsheet too and he is not good with computers, being very dyslexic.

    So I figure I am best off using words that automatically generates a value in the next column that can be multiplied with the market price (H) to make me a hopeful valuation (I). There are probably easier ways of doing this. Like missing a step out, but that is way beyond me and I like to keep things simple.

    At present, in my head, the depreciation values are As New (0.80) Good (0.60), Fair (0.40), Rough (0.2) and Kaput (0).

    Little help please, (if you have time) gratefully received.
  • Twinkle 15 Jul 2009 14:32:22 542 posts
    Seen 3 years ago
    Registered 16 years ago
    If your value is in A1:

    =IF(A1="As New",0.8,IF(A1="Good",0.6,IF(A1="Fair",0.4,IF(A1="Rough",0.2,0))))
Sign in or register to reply

Sometimes posts may contain links to online retail stores. If you click on one and make a purchase we may receive a small commission. For more information, go here.