Charts with formula linked figures

[Hrishi]

Hello, I have two queries regarding chart.
1) I have a table with figures in Rupees (INR) and i want a histograph stating figures in "LAKH" (LAkh is 1/10 million). So basically if i have value in table say 5000000.00, i want my cart to show figure 50. (50=5000000/100000)
and it should be dynamic. If i change figures in table, it should reflect in chart.

2) numbers in above table need not be sorted, but i want my histograph to be in ascending order or descending order.

pls see images attached.

 

[Jimmypop]

Hello! I hope I can help you with your queries.

1. To convert the figures from Rupees to Lakh and reflect the changes dynamically in the chart, you can use a formula in Excel or Google Sheets to divide the value by 100,000. For example, if your value is in cell A1, you can use the formula =A1/100000 to get the value in Lakhs. You can then create a histogram chart in Excel or Google Sheets using these converted values.

To make the chart dynamic, you can use named ranges in Excel or named ranges or dynamic ranges in Google Sheets. By using named ranges, your chart will automatically update when you add or remove values from the table.

1. To sort the histogram chart in ascending or descending order, you can use the sorting options in Excel or Google Sheets. In Excel, you can right-click on the chart and select "Select Data", then click on the "Edit" button under "Horizontal (Category) Axis Labels" and choose the cells with the labels in the desired order. In Google Sheets, you can select the chart and use the "Sort data by" option in the "Customize" tab to choose the column and order you want to sort by.

I hope this helps! Let me know if you have any more questions.

 

[Hrishi]

Hello Jimmy, Thank you for reverting. However, i am not able to implement it correctly. Also how input figure will be divided by 100000.0.

about sorting, my input figures will keep changing, wo chart should automatically sort the data, without me sorting data in table.

is it possible?

 

[Jimmypop]

What are you struggling with...

 

[Hrishi]

What are you struggling with...

Using Name manager I named the data, but could not import that to chart.
if you could pls provide step by step action, that might help.

 

[Jimmypop]

Please tell me what you named the data?

Also is it possible for you to use XL2BB to provide some sample data to work with?

 

[Hrishi]

Please tell me what you named the data?

Also is it possible for you to use XL2BB to provide some sample data to work with?

 

[Jimmypop]

You have a blank post...

 

[Hrishi]

APPROVAL NOTE FORMAT.xlsx
	A	B	C	D	E
15	Sr. No.	Vendor	First Offer	Final offer	Ranking
16
17	1	a	₹ 1,000.00	₹ 1,200.00	L 4
18	2	b	₹ 1,500.00	₹ 500.00	L 1
19	3	c	₹ 1,500.00	₹ 1,456.00	L 6
20	4	d	₹ 4,000.00	₹ 900.00	L 2
21	5	e	₹ 3,000.00	₹ 1,050.00	L 3
22	6	f	₹ 6,000.00	₹ 1,225.00	L 5
23	7	g	₹ 5,000.00	₹ 1,822.00	L 10
24	8	h	₹ 3,550.00	₹ 1,954.00	L 11
25	9	j	₹ 4,004.00	₹ 1,784.00	L 9
26	10	k	₹ 4,554.00	₹ 1,775.00	L 8
27	11	l	₹ 5,660.00	₹ 1,464.00	L 7
Approval Note

Cell Formulas
Range	Formula
E17:E27	E17	=+"L " &RANK(D17,$D$17:$D$27,1)

Named Ranges
Name	Refers To	Cells
FINALOFFER	='Approval Note '!$D$17:$D$27	E17:E27

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E17:E27	Cell Value	="L 5"	text	NO
E17:E27	Cell Value	="L 4"	text	NO
E17:E27	Cell Value	="L 3"	text	NO
E17:E27	Cell Value	="L 1"	text	NO
E17:E27	Cell Value	="L 2"	text	NO

 

[Jimmypop]

The L 4 and L1... Is that the Lakh and is that what you want in the chart