+ Reply to Thread
Results 1 to 11 of 11

take a cell value of nixed format and multiply another cell of mixed format

  1. #1
    Registered User
    Join Date
    01-03-2014
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    71

    take a cell value of nixed format and multiply another cell of mixed format

    i have 3 columns.
    A b &c
    A= .120kva (numeric values change)
    B=100ohm fail OR 100ohm pass (numeric values change)

    what Im trying to do is check to see if column B reads pass or fail
    If B reads pass then multiply cell A by 1000 and show result as (numeric) WATTS
    if B reads fail then show result as 0 watts ( or leave blank)
    how do i get excel to ignore the KVA in cloumn A and the numeric value in column B.
    Neen on this all day and cant for the life of me see how to do it
    Any help is greatly received
    Many thanks
    Allen

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: take a cell value of nixed format and multiply another cell of mixed format

    try
    =IF(ISERROR(SEARCH("Pass",B1)),"",LEFT(TRIM(A1),LEN(TRIM(A1))-3)*1000&" WATTS")

    is that always kva?
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Registered User
    Join Date
    01-03-2014
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    71

    Re: take a cell value of nixed format and multiply another cell of mixed format

    I need to take P as the value to convert (multiply by 1000 to show watts) based on the value in Q saying pass or fail
    if Q says pass then multiply it if Q says fail then show a 0 (zero).
    Im sure there is a way of doing it other than the LEN function but im still learning (rapidly)

    Many thanks
    Allen
    Attached Files Attached Files

  4. #4
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: take a cell value of nixed format and multiply another cell of mixed format

    the lookup ???

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    what will be the issue with the Len()?
    are you entering manually those kva?

  5. #5
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: take a cell value of nixed format and multiply another cell of mixed format

    Nice job vlady,

    I would use =SUBSTITUTE(A1,"kva","") for the kva part.

    Lewis

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: take a cell value of nixed format and multiply another cell of mixed format

    Try:
    Please Login or Register  to view this content.
    Quang PT

  7. #7
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: take a cell value of nixed format and multiply another cell of mixed format

    are you entering manually those kva?
    I prefer the lookup if typo happens

  8. #8
    Registered User
    Join Date
    01-03-2014
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    71

    Re: take a cell value of nixed format and multiply another cell of mixed format

    No its imported from my other project it and electrical test project ( downloaded from a test machine parsed to a sheet that puts it in this order ( machine download format is not as you want to read it. then seperates it in either a pass sheet a fail sheet and or a repair sheet. creates a summary of the items {count passed failed repaired] and generates an invoice on the given parameters) but the .120KVA for wattage ( ok I understand it) but it is like saying instead of you drill being say 700 WATTS is 0.7kva which confuses people (apparantly ???? dont personally see how but thats just me lol)

  9. #9
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: take a cell value of nixed format and multiply another cell of mixed format

    lol, i don' understand kva also but i know watts.

    you have options so just choose or try them all and look for what best fits since we don't see all your data.

  10. #10
    Registered User
    Join Date
    01-03-2014
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    71

    Re: take a cell value of nixed format and multiply another cell of mixed format

    Many thanks guys
    vlady (FYI if you look at watts and divide by a 1000 that is the KVA {kilo volt amps} stupid i know but thats not for this forum lol

  11. #11
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: take a cell value of nixed format and multiply another cell of mixed format

    lol, but surely there's a use for having KVA on those electronic/current computations.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Mixed dates that need to all be the same format
    By aigo in forum Excel General
    Replies: 2
    Last Post: 12-06-2013, 06:48 AM
  2. check for date in mixed text/date and format cell.. Help!
    By berbchid in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-24-2013, 03:42 AM
  3. Replies: 1
    Last Post: 04-18-2012, 11:18 AM
  4. [SOLVED] format painter wont copy mixed font colors from one cell to next
    By dgc49 in forum Excel General
    Replies: 6
    Last Post: 06-08-2006, 08:40 PM
  5. Replies: 1
    Last Post: 06-27-2005, 06:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1