Formula to return a value based on two cells and on entering data

N

noreendeasy

New Member
Joined
Jul 16, 2014
Messages
32
Hi there,

I have a spreadsheet that I would like to return 1. Text String based on which cells are entered and 2. Calculated Formula. Both are in different cells and I would like them merged on data is entered.

Its hard to explain but I'll show you what I mean

This starts blank until we get an exception and then we need to figure out whats causing it

[TABLE="width: 425"]
<TBODY>[TR]
[TD][/TD]
[TD]A</SPAN>
[/TD]
[TD]B</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1</SPAN>
[/TD]
[TD]Movement</SPAN>
[/TD]
[TD]Movement Value</SPAN>
[/TD]
[TD]BPS</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2</SPAN>
[/TD]
[TD]Net Gain/Loss </SPAN>
[/TD]
[TD="align: right"]0.16</SPAN>
[/TD]
[TD="align: right"]0.02</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]3</SPAN>
[/TD]
[TD]Income</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4</SPAN>
[/TD]
[TD]Forward Contracts</SPAN>
[/TD]
[TD="align: right"]0.1</SPAN>
[/TD]
[TD="align: right"]0.01</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5</SPAN>
[/TD]
[TD]Total</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]0.03</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6</SPAN>
[/TD]
[TD]Narrative:</SPAN>
[/TD]
[TD]Main Driver of Movement is _____ in this case (A Value) Net Gain/Loss of (C Value) ____ .01 and Forward Contracts of .01 giving an overall movement of _____.03 (C Total)</SPAN>
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
So to explain above what I need is to formula the Narrative:

The user pastes values in B which calculates C.

The Narrative needs to return whats in A and C (as above) based on what the user pastes in B.

So if this changed to:
[TABLE="width: 425"]
<TBODY>[TR]
[TD][/TD]
[TD]A</SPAN>
[/TD]
[TD]B</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1</SPAN>
[/TD]
[TD]Movement</SPAN>
[/TD]
[TD]Movement Value</SPAN>
[/TD]
[TD]BPS</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2</SPAN>
[/TD]
[TD]Net Gain/Loss </SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3</SPAN>
[/TD]
[TD]Income</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4</SPAN>
[/TD]
[TD]Forward Contracts</SPAN>
[/TD]
[TD="align: right"]0.2</SPAN>
[/TD]
[TD="align: right"]0.03</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5</SPAN>
[/TD]
[TD]Total</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]0.03</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]</SPAN>
[/TD]
[TD]</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

The Narrative would need to be automatically updated to using a formula : Is this possible?
[TABLE="width: 425"]
<TBODY>[TR]
[TD="align: right"]6</SPAN>
[/TD]
[TD]Narrative:</SPAN>
[/TD]
[TD]Main Driver of Movement is Income of .03 giving an overall total of .03
</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


 
Last edited:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
Your example isn't very clear (your first example the numbers don't seem to match your data), however, I *believe* this is what you are looking for.

="Main Driver of Movement is " & IF(C2<>0,CHAR(10) & A2&" of " & C2,"") & IF(COUNT(C2:C3)>1, " and ", "") & IF(C3<>0,CHAR(10) & A3&" of " & C3,"") & IF(COUNT(C2:C4)>1," and ", "")& IF(C4<>0,CHAR(10) & A4&" of " & C4,"") & " " & CHAR(10) & "giving an overall movement of " & C5

NOTE: the Char(10) characters will insert line breaks and need Wrap Text turned on. If you do not want line breaks, use this formula...

="Main Driver of Movement is " & IF(C2<>0, A2&" of " & C2,"") & IF(COUNT(C2:C3)>1, " and ", "") & IF(C3<>0, A3&" of " & C3,"") & IF(COUNT(C2:C4)>1," and ", "")& IF(C4<>0,A4&" of " & C4,"") & " " & "giving an overall movement of " & C5
 
Upvote 0
Can you explain this and maybe I could understand it and edit it myself? Or else what is it you don't understand from my data? The BPS column isn't meant to add up its just an example....

Possibly this is better explained:

[TABLE="class: cms_table"]
<TBODY>[TR]
[TD][/TD]
[TD]A</SPAN>
[/TD]
[TD]B</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1</SPAN>
[/TD]
[TD]Movement</SPAN>
[/TD]
[TD]Movement Value</SPAN>
[/TD]
[TD]BPS</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2</SPAN>
[/TD]
[TD]Net Gain/Loss </SPAN>
[/TD]
[TD][/TD]
[TD]this column is a formula based on columns Bs data
[/TD]
[/TR]
[TR]
[TD="align: right"]3 </SPAN>
[/TD]
[TD]Income</SPAN>
[/TD]
[TD][/TD]
[TD]=0.2*Nav*10000[/TD]
[/TR]
[TR]
[TD="align: right"]4</SPAN>
[/TD]
[TD]Forward Contracts</SPAN>
[/TD]
[TD="align: right"]0.2</SPAN>
[/TD]
[TD="align: right"]0.03</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5</SPAN>
[/TD]
[TD]Total</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]0.03</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]</SPAN>
[/TD]
[TD]</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
Thanks, Noreen
 
Last edited:
Upvote 0
In your first example, you have 0.02 in the data for Net Gain/Loss but use 0.01 in the narrative.
In the second example, you have nothing for Income, but list 0.03 which is shown as the data for Forward Contracts.

Basically, what my formula does is check if the value in column C <> 0 and if so it lists the column header from A and the value from C. The countif determines whether the word AND should be used. If you have more specific questions or if it is not working as required, please let me know.
 
Upvote 0
Hi Sorry I mixed it up,

You're completely spot on about what I need to do, it just doesn't seem to work.

I will use my real data it might explain it actually easier:

[TABLE="width: 515"]
<TBODY>[TR]
[TD]Can you help create the formula for this please?

Prior Day NAV</SPAN>[/TD]
[TD]14.26309</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Movement</SPAN>[/TD]
[TD]Movements</SPAN>[/TD]
[TD]BPS[/TD]
[/TR]
[TR]
[TD]PERIOD INCOME</SPAN>[/TD]
[TD] [/TD]
[TD] 0.00</SPAN>[/TD]
[/TR]
[TR]
[TD]PERIOD AMORT/ACCRET</SPAN>[/TD]
[TD] [/TD]
[TD] 0.00</SPAN>[/TD]
[/TR]
[TR]
[TD]PERIOD INFLATION COMPENSATION</SPAN>[/TD]
[TD] [/TD]
[TD] 0.00</SPAN>[/TD]
[/TR]
[TR]
[TD]PERIOD EXPENSES </SPAN>[/TD]
[TD] [/TD]
[TD] 0.00</SPAN>[/TD]
[/TR]
[TR]
[TD]INCOME DISTRIBUTIONS</SPAN>[/TD]
[TD] [/TD]
[TD] 0.00</SPAN>[/TD]
[/TR]
[TR]
[TD]EXCHANGE GAIN/LOSS RECLASSED AS INCOME</SPAN>[/TD]
[TD]-0.00724</SPAN>[/TD]
[TD]-0.05</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PERIOD REALIZED GAIN/LOSS </SPAN>[/TD]
[TD] [/TD]
[TD] 0.00</SPAN>[/TD]
[/TR]
[TR]
[TD]GAIN/LOSS EFFECT OF TRANSACTIONS </SPAN>[/TD]
[TD] [/TD]
[TD] 0.00</SPAN>[/TD]
[/TR]
[TR]
[TD]NET GAIN/LOSS EFFECT </SPAN>[/TD]
[TD]-0.04953</SPAN>[/TD]
[TD] -0.35</SPAN>[/TD]
[/TR]
[TR]
[TD]GAIN/LOSS DISTRIBUTIONS</SPAN>[/TD]
[TD] [/TD]
[TD] 0.00</SPAN>[/TD]
[/TR]
[TR]
[TD]CAPITAL SHARE ACTIVITY</SPAN>[/TD]
[TD] [/TD]
[TD] 0.00</SPAN>[/TD]
[/TR]
[TR]
[TD]SUBSCRIPTIONS</SPAN>[/TD]
[TD] [/TD]
[TD] 0.00</SPAN>[/TD]
[/TR]
[TR]
[TD]REDEMPTIONS</SPAN>[/TD]
[TD] [/TD]
[TD] 0.00</SPAN>[/TD]
[/TR]
[TR]
[TD]NET OF CAPITAL SHARE ACTIVITY</SPAN>[/TD]
[TD] [/TD]
[TD] 0.00</SPAN>[/TD]
[/TR]
[TR]
[TD]PRELIMINARY NET ASSETS</SPAN>[/TD]
[TD] [/TD]
[TD] 0.00</SPAN>[/TD]
[/TR]
[TR]
[TD]EFFECT OF PRICE CHANGES ON HOLDINGS</SPAN>[/TD]
[TD] [/TD]
[TD] 0.00</SPAN>[/TD]
[/TR]
[TR]
[TD]ADJUSTMENT FOR AMORT/ACCRET</SPAN>[/TD]
[TD] [/TD]
[TD] 0.00</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RECEIVABLES/PAYABLE</SPAN>[/TD]
[TD] [/TD]
[TD] 0.00</SPAN>[/TD]
[/TR]
[TR]
[TD]INCOME</SPAN>[/TD]
[TD] [/TD]
[TD] 0.00</SPAN>[/TD]
[/TR]
[TR]
[TD]CCT FORWARD CONTRACTS</SPAN>[/TD]
[TD] [/TD]
[TD] 0.00</SPAN>[/TD]
[/TR]
[TR]
[TD]CCT SPOT CONTRACTS</SPAN>[/TD]
[TD] [/TD]
[TD] 0.00</SPAN>[/TD]
[/TR]
[TR]
[TD]ADJUSTMENT FOR GLOBAL FUTURES</SPAN>[/TD]
[TD="align: right"]0.14463</SPAN>[/TD]
[TD] 1.01</SPAN>[/TD]
[/TR]
[TR]
[TD]OTHER MISCELLANEOUS ADJUSTMENTS</SPAN>[/TD]
[TD] [/TD]
[TD] 0.00</SPAN>[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD][/TD]
[TD] 0.62
</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]
 
Upvote 0
Thank you, the actual data makes more sense and I see why my formula doesn't work well (there are simply too many rows for my current method)
Are these always the options listed or are the items in the Movement list variable? That is, always X number of rows with the Total in the same place?
 
Upvote 0
BiocideJ said:
Thank you, the actual data makes more sense and I see why my formula doesn't work well (there are simply too many rows for my current method)
Are these always the options listed or are the items in the Movement list variable? That is, always X number of rows with the Total in the same place?
Click to expand...

Yes these are the only options and the total is always in the same place, it comes from a report.
 
Upvote 0
Are you open to using a VBA solution? I don't think a native Excel formula is going to be sufficient in this case.
 
Upvote 0
Yes VBA is ok, I have basic / ok understanding of it - use it often for Macros! Cna you help, I wouldn't know how to use it in this instance? Thanking you kindly
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Return A Cell Value Based On the First Occurrence of data
Replies
5
Views
3K
Fluff
Fluff
C
  • Question Question
Return closest date based on three criteria being true, or return "no date found"
Replies
8
Views
245
chris_bosten
C
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
gmooney
  • Solved
Extract Exact Date from cell that is a formula using textsplit
Replies
2
Views
185
gmooney
gmooney
D
  • Solved
Find a cell in a table, and return the value above
Replies
3
Views
345
Alex Blakenburg
A

Forum statistics

Threads
1,222,405
Messages
6,165,867
Members
451,989
Latest member
DannyBoy1977

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top