Search results

Hello Susmitha, As you know, a ratio requires two numbers to calculate. So I am not sure what to make of your comment that you do not have numeric data. I am assuming there is more to your question... so will wait for further clarification. Cheers, Sajan.

Welcome to the forum! I am assuming you have some numeric data you want to divide as in your post. Feel free to upload a sample workbook, with any additional explanations / clarifications. That would make it easier to help you. Cheers, Sajan.

Hi Jaine, I always forget about SUMIF...! So, here is another formula you could use (with the same results): Put in cell T4 and copy down =SUMPRODUCT(SUMIF(AwardCodes,M4:Q4,AwardRates))*$R4*52 Cheers, Sajan.

Hello Jaine, I have added a new formula in the "Forum Formula" column: =SUM(MMULT(N(AwardCodes=$M5:$Q5), TRANSPOSE(COLUMN($M5:$Q5)^0))*AwardRates)*$R5*52 entered with Ctrl + Shift + Enter I have also added some condition formatting on the NI column, based on a helper cell called "NI Alerts"...

Hello Jaine, I will look at the uploaded file and come back with any questions. Cheers, Sajan.

Hi Jaine, Data Validation in Excel requires specific rules to be followed. (For example, dynamic arrays are not supported directly in the DV functionality.) That is why I suggested use of a helper cell above. Please note that a formula based DV can always be defeated easily by a user. If...

Hello Jaine, Can you elaborate on the results you got when you tried my formula above? That would help us to help you better. It would be best if you can describe all of your requirements in one place. (From another post of yours, I saw a comment about a maximum of two input codes that will...

Hi Tom, Welcome to the forum! You are better off restructuring your data so that you do not repeat the order qty for each shipment line item. (For example, have an Orders table, and a separate Shipments table.) But, if you wish to keep the existing layout, you could try...

Hi Jaine, Depending on the capabilities of your users, one could create more or less elaborate validation schemes. But you could try the following for a value in A1: =IFERROR(SUMPRODUCT(N(N(ISERR(-MID(SUBSTITUTE($A1," ",""),ROW(OFFSET(A$1,,,LEN(SUBSTITUTE($A1,"...

Hi Jaine, Can you describe in English what you are hoping the formula would do? That would help us give you better direction than attempting to decipher your formula above. Cheers, Sajan.

Hi Jaine, Welcome to the forum! I am not sure I understand what you are looking for... are you wanting to lookup the codes in cells M4, N4, O4 and return the weekly rate from Sheet3 column 3, so as to calculate the award amount? If so, try the following formula in cell S4...

You are welcome! Thanks for the feedback. Glad to help. Not sure if you copied / pasted the formula as HTML. That might explain the change in font.

If you wish to accommodate the "hold" scenario, then you could try the following formula in cell G14 =CHOOSE(SIGN($C14-$E14)+2, "Sell", "Hold", "Buy")

Hello Nitin, Put the following formula in cell F3 and copy down to additional rows: =$E3*(1+IF($C3="B",1,-1)*$L$1) (This assumes that there are only two choices: B and not B (aka S). If there could be other options, then the formula will need to be tweaked.) Regarding the "call" based on...

Hi Lori, Thanks for this challenge and the clever pointers. This was a fun one. Looking forward to reading more posts from you in the future. (I have to admit that while I can see myself eventually stumbling onto some of the solutions you have posted elsewhere, many of them seem to be in...

Hello madocar, If finding your car as SirJB7 outlines above was not a satisfactory experience, you could use functions such as FIND and MATCH to locate stuff, and if needed, feed it to OFFSET.

Hi SirJB7, Thanks for thinking of me for that job, but I typically start my negotiations at 70% commission. For you, I am willing to do it for 50%, which I am sure you would agree is extremely generous. :D By the way, not sure if you are aware of an international donkey service that was...

Hi Nitin2k, Since I am feeling lazy (it is a Friday!) to make up some data, can you upload a sample file with data?

Thanks for that clarification Sam and Debraj about 0s and blanks in the range to calculate. -Sajan.

Hi, I am sure Sam will agree that the following simplification would also work: =MIN(IF('Line data'!C5:C133='Line Data Weekly'!A6,'Line data'!I5:I133)) entered with Ctrl + Shift + Enter Cheers Sajan.

Thanks for the feedback Krysta! Glad to help!

Thanks Debraj! Not sure if it is a regional setting, but on my version of Excel, a row-wise array is delimited by ";" (semi-colon) and a column-wise array is delimited by "," (comma) Narayan has agreed to write up an explanation for these formulas. So watch this space for that! -Sajan.

Thanks for the feedback! You are welcome! Glad to help! -Sajan.

Hi Smallman, I think the OP was looking to return a text value based corresponding to the minimum value found in a range... as such, MIN() by itself would not work. I think your second formula is along the lines of what the OP was looking for. -Sajan.

Hello @jdfjab, Welcome to the forum. Using a technique that Lori shared in the most recent formula challenge, the following array formula is one approach: =LOOKUP(1,1/FREQUENCY(-9^9, IF((Basis="C")*(PriceType=$A343),Multiplier,9^9)), TextValues) enter with Ctrl + Shift + Enter where...